﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
//using System.Linq.Dynamic;
using Aspose.Cells;
using System.IO;
using System.Web;
using System.Data;
using System.Configuration;
using Aden.Util.Database;
using Aden.Util.Common;
using Aden.Model.SiteDIY;
using Aden.Model.SQLReport;
using System.Drawing;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Text.RegularExpressions;


namespace Aden.DAL.ReportLib
{
    public class DownloadReport
    {
        //下载Excel周单
        public static MemoryStream ExportExcelWeeklyMenu(Dictionary<string, string> keyDic)
        {
            try
            {
                //如果传入非空窗口，所有的空窗口就被过滤掉
                string filter = "";
                string filterwd = "";
                if (!string.IsNullOrWhiteSpace(keyDic["windowName"]))
                {
                    filter = " and isnull(M.windowGUID,'')<>'' ";
                    filterwd = " and isnull(N.windowGuid,'')<>'' and isnull(M.windowGUID,'')<>'' ";
                }
                else
                {
                    filter = " and isnull(M.windowGUID,'')='' ";
                    filterwd = " and isnull(N.windowGuid,'')='' and isnull(M.windowGUID,'')='' ";
                }

                //列示一周的日期
                string sql = string.Format("select convert(varchar(10),(DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 0)),23) 'Mon', "
                    + "convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 1)), 23) 'Tue', "
                    + "convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 2)), 23) 'Wed', "
                    + "convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 3)), 23) 'Thu', "
                    + "convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 4)), 23) 'Fri', "
                    + "convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 5)), 23) 'Sat', "
                    + "convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 6)), 23) 'Sun' ", keyDic["startDate"]);

                //周菜单数据
                string sql2 = string.Format("SELECT M.ID, M.COSTCENTERCODE, M.WINDOWGUID,isnull(N.WINDOWNAME,'(none)') WINDOWNAME,CONVERT(VARCHAR(100), M.REQUIREDDATE, 20) AS REQUIREDDATE, M.SOITEMGUID, "
                    + "L.PRODUCTDESC, M.ITEMGUID, M.ITEMCODE, M.ITEMTYPE, M.ITEMNAME_ZH, M.ITEMNAME_EN, M.REQUIREDQTY, "
                    + "M.ITEMCOST, M.OTHERCOST, M.ITEMUNITCODE, M.HEADGUID, M.PRODUCTGUID, M.PURCHASEPOLICY, CONVERT(VARCHAR(100), M.CREATETIME, 20) AS CREATETIME, M.CREATEUSER, "
                    + "CONVERT(VARCHAR(100), M.DELETETIME, 20) AS DELETETIME, M.DELETEUSER,(CASE WHEN M.LINKID = 0 THEN M.ID ELSE M.LINKID END) AS LINKID, "
                    + "datename(weekday, CONVERT(VARCHAR(100), M.REQUIREDDATE, 20)) WEEKDAY,P.ItemColor,convert(int,isnull(P.ItemSpicy,-1)) ItemSpicy FROM MENUORDERHEAD (nolock) AS M "
                    //+ "INNER JOIN SALESORDERITEM (nolock) AS L ON M.SOITEMGUID = L.ITEMGUID AND L.STATUS <> '0' INNER JOIN ProductTypeData K ON K.ID=L.ProductCode LEFT JOIN (select distinct WindowSort as Sort,windowGuid,WindowName,SOItemGuid from CCWindowMeals where deletetime is null) N ON M.WINDOWGUID=N.WINDOWGUID and M.SOITEMGUID=N.SOItemGuid "
                    + "INNER JOIN SALESORDERITEM (nolock) AS L ON M.SOITEMGUID = L.ITEMGUID AND L.STATUS <> '0' LEFT JOIN ProductTypeData K ON K.ID=L.ProductCode LEFT JOIN (select distinct WindowSort as Sort,windowGuid,WindowName,SOItemGuid from CCWindowMeals where deletetime is null) N ON M.WINDOWGUID=N.WINDOWGUID and M.SOITEMGUID=N.SOItemGuid "
                    + "LEFT JOIN TBLITEM P ON P.ITEMCODE=M.ITEMCODE "
                    + "LEFT JOIN (select distinct a.* from TBLITEMCLASS a join TBLITEMCLASS b on a.pguid = b.guid) Q ON P.CATEGORIESClassGUID=Q.GUID "
                    + "WHERE P.DELETETIME IS NULL AND M.COSTCENTERCODE = '{1}' "
                    + "AND M.DELETEUSER IS NULL AND M.REQUIREDDATE BETWEEN convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 0)), 23) "
                    + "AND convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 6)), 23) {2} order by k.Sort,L.PRODUCTDESC,N.Sort,N.WINDOWNAME,M.ItemType,M.ItemCode", keyDic["startDate"], keyDic["costCenterCode"], filter);

                //周菜单大类
                string sql3 = string.Format("select convert(int,row_number() OVER (ORDER BY A.Sort)) 'Index',  isnull(A.WindowName,'(none)') WindowName, "
                    + "A.PRODUCTDESC,Max(A.Count) as Max from (SELECT L.PRODUCTDESC,K.Sort,datename(weekday, CONVERT(VARCHAR(100), M.REQUIREDDATE, 20)) WEEKDAY, "
                    + "Count(datename(weekday, CONVERT(VARCHAR(100), M.REQUIREDDATE, 20))) as Count,N.windowGuid,N.WindowName,N.Sort as windowSort FROM MENUORDERHEAD (nolock) AS M INNER JOIN SALESORDERITEM (nolock) AS L "
                    //+ "ON M.SOITEMGUID = L.ITEMGUID AND L.STATUS <> '0' INNER JOIN ProductTypeData K ON K.ID=L.ProductCode LEFT JOIN (select distinct WindowSort as Sort,windowGuid,WindowName,SOItemGuid from CCWindowMeals where deletetime is null) N ON N.WindowGUID=M.WindowGUID and M.SOITEMGUID=N.SOItemGuid "
                    + "ON M.SOITEMGUID = L.ITEMGUID AND L.STATUS <> '0' LEFT JOIN ProductTypeData K ON K.ID=L.ProductCode LEFT JOIN (select distinct WindowSort as Sort,windowGuid,WindowName,SOItemGuid from CCWindowMeals where deletetime is null) N ON N.WindowGUID=M.WindowGUID and M.SOITEMGUID=N.SOItemGuid "
                    + "WHERE M.COSTCENTERCODE = '{0}' AND M.DELETEUSER IS NULL AND M.REQUIREDDATE BETWEEN convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{1}'), 0)), 23) "
                    + "AND convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{1}'), 6)), 23) {2} group by K.Sort, L.PRODUCTDESC,M.REQUIREDDATE,N.WindowName,N.Sort,N.windowGuid) A "
                    + "group by A.Sort, A.windowSort,A.ProductDesc,A.WindowName order by A.Sort, A.PRODUCTDESC,A.windowSort,A.WindowName", keyDic["costCenterCode"], keyDic["startDate"], filterwd);

                var data = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql);

                var menu = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql2);

                var menuClass = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql3);

                var firstCol = menuClass.AsEnumerable().Select(dr => dr.Field<string>("WindowName")).FirstOrDefault();

                int lstCol = 2;
                if (firstCol == null) lstCol = 1;
                else if (firstCol.Contains("none") || firstCol == null) lstCol = 1;


                //一周的日期
                var tbl1 = from dr in data.AsEnumerable()
                           select new
                           {
                               Mon = dr.Field<string>("Mon"),
                               Tue = dr.Field<string>("Tue"),
                               Wed = dr.Field<string>("Wed"),
                               Thu = dr.Field<string>("Thu"),
                               Fri = dr.Field<string>("Fri"),
                               Sat = dr.Field<string>("Sat"),
                               Sun = dr.Field<string>("Sun")
                           };

                Workbook excel = new Workbook();
                Worksheet dataSheet = excel.Worksheets[0];

                dataSheet.Name = "周采购订单";
                Cells dataCells = dataSheet.Cells;

                Style titleStyle = excel.Styles[excel.Styles.Add()];

                Style sidepanelStyle = excel.Styles[excel.Styles.Add()];
                Style sidepanelStyle2 = excel.Styles[excel.Styles.Add()];
                Style sidepanelStyle3 = excel.Styles[excel.Styles.Add()];

                Style subpanelStyle = excel.Styles[excel.Styles.Add()];

                Style weektitleStyle = excel.Styles[excel.Styles.Add()];
                Style dataStyle = excel.Styles[excel.Styles.Add()];

                #region 设置表头样式和数据样式
                titleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                titleStyle.Font.Size = 28;
                titleStyle.Font.IsBold = true;
                titleStyle.Pattern = BackgroundType.Solid;
                //titleStyle.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#32CD32");

                weektitleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                weektitleStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                weektitleStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                weektitleStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                weektitleStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);

                weektitleStyle.Font.Size = 11;
                weektitleStyle.Font.IsBold = true;
                weektitleStyle.Font.Name = "Calibri";
                weektitleStyle.Pattern = BackgroundType.Solid;
                weektitleStyle.ForegroundColor = System.Drawing.Color.FromArgb(255, 217, 102);

                #endregion

                #region 设置侧边格式

                subpanelStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                subpanelStyle.Font.Size = 11;
                subpanelStyle.Font.Name = "Calibri";
                subpanelStyle.Font.IsBold = true;
                subpanelStyle.Pattern = BackgroundType.Solid;
                subpanelStyle.ForegroundColor = System.Drawing.Color.FromArgb(255, 217, 102);
                subpanelStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                subpanelStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                subpanelStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);


                sidepanelStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                sidepanelStyle.Font.Size = 11;
                sidepanelStyle.Font.Name = "Calibri";

                sidepanelStyle.Font.IsBold = true;
                sidepanelStyle.Pattern = BackgroundType.Solid;
                //sidepanelStyle.ForegroundColor = System.Drawing.Color.Yellow;
                sidepanelStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                sidepanelStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                sidepanelStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);


                sidepanelStyle2.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                sidepanelStyle2.Font.Size = 11;
                sidepanelStyle2.Font.Name = "Calibri";
                sidepanelStyle2.Font.IsBold = true;
                sidepanelStyle2.Pattern = BackgroundType.Solid;
                //sidepanelStyle2.ForegroundColor = System.Drawing.Color.Yellow;
                sidepanelStyle2.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                sidepanelStyle2.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);

                sidepanelStyle3.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                sidepanelStyle3.Font.Size = 11;
                sidepanelStyle3.Font.Name = "Calibri";
                sidepanelStyle3.Font.IsBold = true;
                sidepanelStyle3.Pattern = BackgroundType.Solid;
                //sidepanelStyle3.ForegroundColor = System.Drawing.Color.Yellow;
                sidepanelStyle3.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                sidepanelStyle3.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                sidepanelStyle3.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                #endregion

                #region 单元格格式
                dataStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                dataStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                dataStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                dataStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                dataStyle.Font.Size = 11;
                dataStyle.Font.Name = "Calibri";
                dataStyle.IsTextWrapped = true;
                dataStyle.Pattern = BackgroundType.Solid;

                #endregion


                int j = 0;

                dataCells.Merge(0, 0, 1, lstCol + 7);

                //dataCells[0, j].PutValue(keyDic["costCenterName"] +" 周菜单 ");

                //dataCells[0, j++].SetStyle(titleStyle);

                Array Logo = Array.CreateInstance(typeof(Stream), new int[1] { 1 }, new int[1] { 1 });

                //string logoPath = string.Format(@"Images\Logo\Logo.png");
                string logoPath = string.Format(@"Images\Logo\ReportHead.png");
                logoPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, logoPath);
                Logo.SetValue(Util.Common.FileHelper.FileToStream(logoPath), 1);

                // dataSheet.Pictures.Add(0, 0, (Stream)Logo.GetValue(1),80, 30);
                dataSheet.Pictures.Add(0, 0, (Stream)Logo.GetValue(1), 52, 13);


                dataSheet.Shapes.AddShape(Aspose.Cells.Drawing.MsoDrawingType.TextBox, 0, 3, 2, 30, 8, 300);
                dataSheet.TextBoxes[0].TextHorizontalAlignment = TextAlignmentType.Left;
                dataSheet.TextBoxes[0].TextVerticalAlignment = TextAlignmentType.Center;
                dataSheet.TextBoxes[0].Text = keyDic["costCenterName"];
                dataSheet.TextBoxes[0].Font.Color = System.Drawing.Color.White;
                dataSheet.TextBoxes[0].Font.Size = 24;
                dataSheet.TextBoxes[0].Font.Name = "汉仪旗黑-45S";
                dataSheet.TextBoxes[0].IsFilled = false;
                dataSheet.TextBoxes[0].LineFormat.IsVisible = false;

                dataSheet.Shapes.AddShape(Aspose.Cells.Drawing.MsoDrawingType.TextBox, 0, 12, 0, 0, 5, 510);
                dataSheet.TextBoxes[1].TextHorizontalAlignment = TextAlignmentType.Left;
                dataSheet.TextBoxes[1].TextVerticalAlignment = TextAlignmentType.Center;
                dataSheet.TextBoxes[1].Text = "WEEKLY MENU";
                dataSheet.TextBoxes[1].Font.Color = System.Drawing.Color.White;
                dataSheet.TextBoxes[1].Font.Size = 18;
                dataSheet.TextBoxes[1].Font.Name = "Gotham Bold";
                dataSheet.TextBoxes[1].Font.IsItalic = true;
                dataSheet.TextBoxes[1].FillFormat.ForeColor = System.Drawing.Color.FromArgb(255, 192, 0);
                dataSheet.TextBoxes[1].LineFormat.IsVisible = false;

                //dataCells.Merge(1, 0, 2, lstCol);

                //列表头套系字段
                dataCells[1, 0].PutValue("日期");
                dataCells[1, 0].SetStyle(subpanelStyle);
                dataCells[2, 0].PutValue("星期");
                dataCells[2, 0].SetStyle(subpanelStyle);
                //dataCells.Merge(1, 1, 2, 1);
                dataCells[1, 1].PutValue("套系");
                dataCells[1, 1].SetStyle(subpanelStyle);


                //列表一周的周一至周日
                dataCells[2, lstCol].PutValue("星期一 MON");
                dataCells[2, lstCol + 1].PutValue("星期二 TUE");
                dataCells[2, lstCol + 2].PutValue("星期三 WED");
                dataCells[2, lstCol + 3].PutValue("星期四 THU");
                dataCells[2, lstCol + 4].PutValue("星期五 FRI");
                dataCells[2, lstCol + 5].PutValue("星期六 SAT");
                dataCells[2, lstCol + 6].PutValue("星期日 SUN");

                dataCells[2, lstCol].SetStyle(weektitleStyle);
                dataCells[2, lstCol + 1].SetStyle(weektitleStyle);
                dataCells[2, lstCol + 2].SetStyle(weektitleStyle);
                dataCells[2, lstCol + 3].SetStyle(weektitleStyle);
                dataCells[2, lstCol + 4].SetStyle(weektitleStyle);
                dataCells[2, lstCol + 5].SetStyle(weektitleStyle);
                dataCells[2, lstCol + 6].SetStyle(weektitleStyle);

                //一周的日期
                int i = 0;
                foreach (var item in tbl1)
                {
                    j = lstCol;
                    i++;
                    dataCells[i, j].PutValue(item.Mon);
                    dataCells[i, j++].SetStyle(weektitleStyle);
                    dataCells[i, j].PutValue(item.Tue);
                    dataCells[i, j++].SetStyle(weektitleStyle);
                    dataCells[i, j].PutValue(item.Wed);
                    dataCells[i, j++].SetStyle(weektitleStyle);
                    dataCells[i, j].PutValue(item.Thu);
                    dataCells[i, j++].SetStyle(weektitleStyle);
                    dataCells[i, j].PutValue(item.Fri);
                    dataCells[i, j++].SetStyle(weektitleStyle);
                    dataCells[i, j].PutValue(item.Sat);
                    dataCells[i, j++].SetStyle(weektitleStyle);
                    dataCells[i, j].PutValue(item.Sun);
                    dataCells[i, j++].SetStyle(weektitleStyle);
                }

                int s = 0;
                int e = 0;
                int h = 0;
                foreach (var item in menuClass.AsEnumerable())
                {
                    e += item.Field<int>("Max");
                    int y = item.Field<int>("Index");
                    if (item.Field<int>("Index") == 1)
                    {
                        s = 3;
                        h = 3 + item.Field<int>("Max");
                    }
                    else
                    {
                        s = e - item.Field<int>("Max") + 3;
                        h = s + item.Field<int>("Max");
                    }
                    for (var x = s; x < h; x++)
                    {
                        dataCells[x, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                        if (lstCol == 2)
                            dataCells[x, 1].PutValue(item.Field<string>("windowName"));
                    }
                }

                Array peppers = Array.CreateInstance(typeof(Stream), new int[1] { 3 }, new int[1] { 1 });
                for (int x = 1; x < 4; x++)
                {
                    string ss = string.Format(@"Images\Peppers\p{0}.jpg", x);
                    ss = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, ss);
                    peppers.SetValue(Util.Common.FileHelper.FileToStream(ss), x);
                }


                int menulength = e;

                int a = 3;

                foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Monday")) //menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Monday")
                {
                    for (var b = a; b < menulength + 3; b++)
                    {
                        string product = dataCells[b, 0].StringValue.ToString();
                        string window = "(none)";
                        if (lstCol == 2)
                            window = dataCells[b, 1].StringValue.ToString();
                        string it = item.Field<string>("PRODUCTDESC");
                        string wd = item.Field<string>("WINDOWNAME");
                        if (it != product || wd != window) continue;
                        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                        dataCells[b, lstCol].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));


                        if (item.Field<int>("ItemSpicy") > 0)
                        {
                            dataSheet.Pictures.Add(b, lstCol, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol);
                        }
                        a = b;
                        a++;
                        break;
                    }
                }

                a = 3;
                foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Tuesday"))
                {
                    for (var b = a; b < menulength + 3; b++)
                    {
                        string product = dataCells[b, 0].StringValue.ToString();
                        string window = "(none)";
                        if (lstCol == 2)
                            window = dataCells[b, 1].StringValue.ToString();
                        if (item.Field<string>("PRODUCTDESC") != product || item.Field<string>("WINDOWNAME") != window) continue;
                        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                        dataCells[b, lstCol + 1].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));

                        //菜色辣度
                        if (item.Field<int>("ItemSpicy") > 0)
                        {
                            dataSheet.Pictures.Add(b, lstCol + 1, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol + 1);
                        }


                        a = b;
                        a++;
                        break;
                    }
                }

                a = 3;
                foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Wednesday"))
                {
                    for (var b = a; b < menulength + 3; b++)
                    {

                        string product = dataCells[b, 0].StringValue.ToString();
                        string window = "(none)";
                        if (lstCol == 2)
                            window = dataCells[b, 1].StringValue.ToString();
                        if (item.Field<string>("PRODUCTDESC") != product || item.Field<string>("WINDOWNAME") != window) continue;
                        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                        dataCells[b, lstCol + 2].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));

                        //菜色辣度
                        if (item.Field<int>("ItemSpicy") > 0)
                        {
                            dataSheet.Pictures.Add(b, lstCol + 2, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol + 2);
                        }
                        a = b;
                        a++;
                        break;
                    }
                }

                a = 3;
                foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Thursday"))
                {
                    for (var b = a; b < menulength + 3; b++)
                    {
                        string product = dataCells[b, 0].StringValue.ToString();
                        string window = "(none)";
                        if (lstCol == 2)
                            window = dataCells[b, 1].StringValue.ToString();
                        if (item.Field<string>("PRODUCTDESC") != product || item.Field<string>("WINDOWNAME") != window) continue;
                        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                        dataCells[b, lstCol + 3].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));

                        //菜色辣度
                        if (item.Field<int>("ItemSpicy") > 0)
                        {
                            dataSheet.Pictures.Add(b, lstCol + 3, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol + 3);
                        }

                        a = b;
                        a++;
                        break;
                    }
                }

                a = 3;
                foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Friday"))
                {
                    for (var b = a; b < menulength + 3; b++)
                    {
                        string product = dataCells[b, 0].StringValue.ToString();
                        string window = "(none)";
                        if (lstCol == 2)
                            window = dataCells[b, 1].StringValue.ToString();
                        if (item.Field<string>("PRODUCTDESC") != product || item.Field<string>("WINDOWNAME") != window) continue;
                        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                        dataCells[b, lstCol + 4].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));

                        //菜色辣度
                        if (item.Field<int>("ItemSpicy") > 0)
                        {
                            dataSheet.Pictures.Add(b, lstCol + 4, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol + 4);
                        }

                        a = b;
                        a++;
                        break;
                    }
                }

                a = 3;
                foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Saturday"))
                {
                    for (var b = a; b < menulength + 3; b++)
                    {
                        string product = dataCells[b, 0].StringValue.ToString();
                        string window = "(none)";
                        if (lstCol == 2)
                            window = dataCells[b, 1].StringValue.ToString();
                        if (item.Field<string>("PRODUCTDESC") != product || item.Field<string>("WINDOWNAME") != window) continue;
                        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                        dataCells[b, lstCol + 5].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));

                        //菜色辣度
                        if (item.Field<int>("ItemSpicy") > 0)
                        {
                            dataSheet.Pictures.Add(b, lstCol + 5, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol + 5);
                        }

                        a = b;
                        a++;
                        break;
                    }
                }

                a = 3;
                foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Sunday"))
                {
                    for (var b = a; b < menulength + 3; b++)
                    {
                        string product = dataCells[b, 0].StringValue.ToString();
                        string window = "(none)";
                        if (lstCol == 2)
                            window = dataCells[b, 1].StringValue.ToString();
                        if (item.Field<string>("PRODUCTDESC") != product || item.Field<string>("WINDOWNAME") != window) continue;
                        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                        dataCells[b, lstCol + 6].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));

                        //菜色辣度
                        if (item.Field<int>("ItemSpicy") > 0)
                        {
                            dataSheet.Pictures.Add(b, lstCol + 6, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol + 6);
                        }
                        a = b;
                        a++;
                        break;
                    }
                }

                for (int c = 3; c < menulength + 3; c++)
                {
                    var cur = dataCells[c, 0].StringValue.ToString();
                    var nex = dataCells[c + 1, 0].StringValue.ToString();
                }

                int bgn = 3;
                int egn = 1;
                int egn_bak = 1;
                dataCells[3, 8].PutValue(1);
                for (int c = 3; c < menulength + 3; c++)
                {
                    var cur = dataCells[c, 0].StringValue.ToString();
                    var nex = dataCells[c + 1, 0].StringValue.ToString();

                    if (cur == nex)
                    {
                        egn++;
                        continue;
                    }
                    else if (cur != nex)
                    {
                        dataCells.Merge(bgn, 0, egn, 1);
                        if (bgn > 3)
                            dataCells[bgn, 8].PutValue(dataCells[bgn - egn_bak, 8].IntValue + 1);

                        bgn = c + 1;
                        egn_bak = egn;
                        egn = 1;
                    }
                }
                for (int c = 3; c < menulength + 3; c++)
                {
                    if (!string.IsNullOrWhiteSpace(dataCells[c, 8].StringValue) && string.IsNullOrWhiteSpace(dataCells[c + 1, 8].StringValue))
                        dataCells[c + 1, 8].PutValue(dataCells[c, 8].IntValue);
                    if (dataCells[c, 8].IntValue % 2 != 0)
                    {
                        dataStyle.ForegroundColor = System.Drawing.Color.FromArgb(255, 242, 204);
                    }
                    else
                    {
                        dataStyle.ForegroundColor = System.Drawing.Color.FromArgb(255, 230, 153);
                    }
                    dataCells[c, 0].SetStyle(dataStyle);
                    dataCells[c, 1].SetStyle(dataStyle);
                    dataCells[c, 2].SetStyle(dataStyle);
                    dataCells[c, 3].SetStyle(dataStyle);
                    dataCells[c, 4].SetStyle(dataStyle);
                    dataCells[c, 5].SetStyle(dataStyle);
                    dataCells[c, 6].SetStyle(dataStyle);
                    dataCells[c, 7].SetStyle(dataStyle);
                    dataCells[c, 8].SetStyle(dataStyle);
                }

                int bgn2 = 3;
                int egn2 = 1;
                for (int c = 3; c < menulength + 3; c++)
                {
                    var cur = dataCells[c, 1].StringValue.ToString();
                    var nex = dataCells[c + 1, 1].StringValue.ToString();

                    if (cur == nex)
                    {
                        egn2++;
                        continue;
                    }
                    else if (cur != nex)
                    {
                        dataCells.Merge(bgn2, 1, egn2, 1);
                        bgn2 = c + 1;
                        egn2 = 1;
                    }
                }

                dataSheet.AutoFitRows();
                dataCells.SetRowHeight(0, 95);
                dataCells.SetColumnWidth(lstCol, (int)dataCells.GetColumnWidth(lstCol) + 10);
                dataCells.SetColumnWidth(lstCol + 1, (int)dataCells.GetColumnWidth(lstCol + 1) + 10);
                dataCells.SetColumnWidth(lstCol + 2, (int)dataCells.GetColumnWidth(lstCol + 2) + 10);
                dataCells.SetColumnWidth(lstCol + 3, (int)dataCells.GetColumnWidth(lstCol + 3) + 10);
                dataCells.SetColumnWidth(lstCol + 4, (int)dataCells.GetColumnWidth(lstCol + 4) + 10);
                dataCells.SetColumnWidth(lstCol + 5, (int)dataCells.GetColumnWidth(lstCol + 5) + 10);
                dataCells.SetColumnWidth(lstCol + 6, (int)dataCells.GetColumnWidth(lstCol + 6) + 10);
                dataCells.DeleteColumn(8);
                return excel.SaveToStream();

                //DownloadExcelFile(excel.SaveToStream());
                //var excelName = (DateTime.Now.ToString("yyyy-MM-dd HH.mm.ss") + "WeeklyReport.xls").ToString();
                //excel.Save("C:/" + excelName);
                //System.Diagnostics.Process.Start("C:/" + excelName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static MemoryStream ExportSiteExcelWeeklyMenu(Dictionary<string, string> keyDic)
        {

            try
            {


                //列示一周的日期
                string sql = string.Format("select convert(varchar(10),(DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 0)),23) 'Mon', "
                    + "convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 1)), 23) 'Tue', "
                    + "convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 2)), 23) 'Wed', "
                    + "convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 3)), 23) 'Thu', "
                    + "convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 4)), 23) 'Fri', "
                    + "convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 5)), 23) 'Sat', "
                    + "convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{0}'), 6)), 23) 'Sun' ", keyDic["startDate"]);

                //周菜单数据
                string sql2 = string.Format("select SiteGUID,MealDate,MealType,WindowType,FoodNames,FoodNames_EN,Sort,MealCode,WindowType_EN,isnull(EatQty,2) EatQty "
                    + "from SUZCATMenu where SiteGUID = '02ADA113-1B31-48C5-AAC3-791FDC5448BF' "
                    + "and MealDate between '2019-03-18 00:00:00.000' and '2019-03-22 00:00:00.000' and DeleteTime is null "
                    + "order by MealCode, Sort, MealDate", keyDic["startDate"], keyDic["costCenterCode"]);

                var data = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql);

                var menu = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql2);

                //var menuClass = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql3);

                //var firstCol = menuClass.AsEnumerable().Select(dr => dr.Field<string>("WindowName")).FirstOrDefault();

                //int lstCol = 2;
                //if (firstCol.Contains("none")) lstCol = 1;


                //一周的日期
                var tbl1 = from dr in data.AsEnumerable()
                           select new
                           {
                               Mon = dr.Field<string>("Mon"),
                               Tue = dr.Field<string>("Tue"),
                               Wed = dr.Field<string>("Wed"),
                               Thu = dr.Field<string>("Thu"),
                               Fri = dr.Field<string>("Fri"),
                               Sat = dr.Field<string>("Sat"),
                               Sun = dr.Field<string>("Sun")
                           };

                Workbook excel = new Workbook();
                Worksheet dataSheet = excel.Worksheets[0];

                dataSheet.Name = "周采购订单";
                Cells dataCells = dataSheet.Cells;

                Style titleStyle = excel.Styles[excel.Styles.Add()];
                Style dataStyle = excel.Styles.CreateBuiltinStyle(BuiltinStyleType.Normal);

                Style sidepanelStyle = excel.Styles[excel.Styles.Add()];
                Style sidepanelStyle2 = excel.Styles[excel.Styles.Add()];
                Style sidepanelStyle3 = excel.Styles[excel.Styles.Add()];

                Style subpanelStyle = excel.Styles[excel.Styles.Add()];

                Style weektitleStyle = excel.Styles[excel.Styles.Add()];

                #region 设置表头样式和数据样式
                titleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                titleStyle.Pattern = BackgroundType.Solid;
                //titleStyle.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#32CD32"); //System.Drawing.Color.LimeGreen;

                weektitleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                weektitleStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                weektitleStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                weektitleStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                weektitleStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);

                weektitleStyle.Font.Size = 11;
                weektitleStyle.Font.IsBold = false;
                weektitleStyle.Pattern = BackgroundType.Solid;
                //weektitleStyle.ForegroundColor = System.Drawing.Color.Yellow;

                #endregion

                #region 设置侧边格式

                subpanelStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                subpanelStyle.Font.Size = 11;
                subpanelStyle.Font.Name = "宋体";
                subpanelStyle.Font.IsBold = true;
                //subpanelStyle.Pattern = BackgroundType.Solid;
                //subpanelStyle.ForegroundColor = System.Drawing.Color.Black;
                subpanelStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                subpanelStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                subpanelStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);


                sidepanelStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                sidepanelStyle.Font.Size = 11;
                sidepanelStyle.Font.Name = "宋体";

                sidepanelStyle.Font.IsBold = true;
                sidepanelStyle.Pattern = BackgroundType.Solid;
                sidepanelStyle.ForegroundColor = System.Drawing.Color.Yellow;
                sidepanelStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                sidepanelStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                sidepanelStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);


                sidepanelStyle2.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                sidepanelStyle2.Font.Size = 11;
                sidepanelStyle2.Font.Name = "宋体";
                sidepanelStyle2.Font.IsBold = true;
                sidepanelStyle2.Pattern = BackgroundType.Solid;
                //sidepanelStyle2.ForegroundColor = System.Drawing.Color.Yellow;
                sidepanelStyle2.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                sidepanelStyle2.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);

                sidepanelStyle3.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                sidepanelStyle3.Font.Size = 11;
                sidepanelStyle3.Font.Name = "宋体";
                sidepanelStyle3.Font.IsBold = true;
                sidepanelStyle3.Pattern = BackgroundType.Solid;
                //sidepanelStyle3.ForegroundColor = System.Drawing.Color.Yellow;
                sidepanelStyle3.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                sidepanelStyle3.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                sidepanelStyle3.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                #endregion

                #region 单元格格式
                dataStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                dataStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                dataStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                dataStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                dataStyle.Font.Size = 11;
                dataStyle.Font.Name = "宋体";
                dataStyle.IsTextWrapped = true;


                #endregion

                int j = 0;

                dataCells.Merge(0, 0, 1, 2);

                dataCells[0, j].PutValue(" WEEKLY MENU ");

                dataCells[0, j++].SetStyle(titleStyle);

                Array Logo = Array.CreateInstance(typeof(Stream), new int[1] { 1 }, new int[1] { 1 });

                //string logoPath = string.Format(@"Images\Logo\Logo.png");
                //logoPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, logoPath);
                //Logo.SetValue(Util.Common.FileHelper.FileToStream(logoPath), 1);

                //dataSheet.Pictures.Add(0, 0, (Stream)Logo.GetValue(1), 100, 30);

                //dataCells.Merge(1, 0, 2, lstCol);

                //列表头套系字段
                //dataCells[1, 0].PutValue("时间");
                //dataCells[1, 0].SetStyle(subpanelStyle);
                //dataCells[2, 0].PutValue("餐次");
                //dataCells[2, 0].SetStyle(subpanelStyle);
                //dataCells.Merge(1, 1, 2, 1);
                //dataCells[1, 1].PutValue("套系");
                //dataCells[1, 1].SetStyle(subpanelStyle);


                //列表一周的周一至周日
                //dataCells[2, 2].PutValue("星期一");
                //dataCells[2, 3].PutValue("星期二");
                //dataCells[2, 4].PutValue("星期三");
                //dataCells[2, 5].PutValue("星期四");
                //dataCells[2, 6].PutValue("星期五");
                //dataCells[2, 7].PutValue("星期六");
                //dataCells[2, 8].PutValue("星期日");

                //dataCells[2, 2].SetStyle(weektitleStyle);
                //dataCells[2, 3].SetStyle(weektitleStyle);
                //dataCells[2, 4].SetStyle(weektitleStyle);
                //dataCells[2, 5].SetStyle(weektitleStyle);
                //dataCells[2, 6].SetStyle(weektitleStyle);
                //dataCells[2, 7].SetStyle(weektitleStyle);
                //dataCells[2, 8].SetStyle(weektitleStyle);

                //一周的日期
                int i = 0;
                foreach (var item in tbl1)
                {
                    j = 2;
                    i++;

                    dataCells[i, j].PutValue("Monday " + "\n" + item.Mon);
                    dataCells[i, j++].SetStyle(weektitleStyle);
                    dataCells[i, j].PutValue("Tuesday " + "\n" + item.Tue);
                    dataCells[i, j++].SetStyle(weektitleStyle);
                    dataCells[i, j].PutValue("Wednesday " + "\n" + item.Wed);
                    dataCells[i, j++].SetStyle(weektitleStyle);
                    dataCells[i, j].PutValue("Thursday " + "\n" + item.Thu);
                    dataCells[i, j++].SetStyle(weektitleStyle);
                    dataCells[i, j].PutValue("Friday" + "\n" + item.Fri);
                    dataCells[i, j++].SetStyle(weektitleStyle);

                    //dataCells[i, j].PutValue(item.Sat);
                    //dataCells[i, j++].SetStyle(weektitleStyle);
                    //dataCells[i, j].PutValue(item.Sun);
                    //dataCells[i, j++].SetStyle(weektitleStyle);
                }

                #region no use 

                //int s = 0;
                //int e = 0;
                //int h = 0;
                //foreach (var item in menuClass.AsEnumerable())
                //{
                //    e += item.Field<int>("Max");
                //    int y = item.Field<int>("Index");
                //    if (item.Field<int>("Index") == 1)
                //    {
                //        s = 3;
                //        h = 3 + item.Field<int>("Max");
                //    }
                //    else
                //    {
                //        s = e - item.Field<int>("Max") + 3;
                //        h = s + item.Field<int>("Max");
                //    }
                //    for (var x = s; x < h; x++)
                //    {
                //        dataCells[x, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                //        if (lstCol == 2)
                //            dataCells[x, 1].PutValue(item.Field<string>("windowName"));
                //    }
                //    if (y == 1)
                //    {


                //        dataCells[3, 0].SetStyle(sidepanelStyle3);

                //        if (lstCol == 2)
                //        {
                //            sidepanelStyle3.ForegroundColor = System.Drawing.Color.DodgerBlue;
                //            sidepanelStyle3.Font.Color = System.Drawing.Color.Red;
                //            dataCells[3, 1].SetStyle(sidepanelStyle3);
                //        }
                //        for (var x = s; x < h; x++)
                //        {
                //            if (x != 3)
                //            {
                //                sidepanelStyle2.Font.Color = System.Drawing.Color.Yellow;
                //                sidepanelStyle2.ForegroundColor = System.Drawing.Color.Yellow;
                //                dataCells[x, 0].SetStyle(sidepanelStyle2);
                //                if (lstCol == 2)
                //                {
                //                    sidepanelStyle2.Font.Color = System.Drawing.Color.DodgerBlue;
                //                    sidepanelStyle2.ForegroundColor = System.Drawing.Color.DodgerBlue;
                //                    sidepanelStyle2.Font.Color = System.Drawing.Color.DodgerBlue;
                //                    dataCells[x, 1].SetStyle(sidepanelStyle2);
                //                }

                //            }
                //        }
                //    }
                //    else if (y > 1)
                //    {
                //        for (var x = s; x < h; x++)
                //        {
                //            if (x == e - item.Field<int>("Max") + 3)
                //            {
                //                if (lstCol == 2)
                //                    sidepanelStyle.ForegroundColor = System.Drawing.Color.DodgerBlue;
                //                sidepanelStyle.Font.Color = System.Drawing.Color.Red;
                //                dataCells[x, 1].SetStyle(sidepanelStyle);
                //            }
                //            else
                //            {
                //                if (lstCol == 2)
                //                    sidepanelStyle2.ForegroundColor = System.Drawing.Color.DodgerBlue;
                //                sidepanelStyle2.Font.Color = System.Drawing.Color.DodgerBlue;
                //                dataCells[x, 1].SetStyle(sidepanelStyle2);
                //            }
                //        }
                //    }
                //}

                //Array peppers = Array.CreateInstance(typeof(Stream), new int[1] { 3 }, new int[1] { 1 });
                //for (int x = 1; x < 4; x++)
                //{
                //    string ss = string.Format(@"Images\Peppers\p{0}.jpg", x);
                //    ss = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, ss);
                //    peppers.SetValue(Util.Common.FileHelper.FileToStream(ss), x);
                //}


                //int menulength = e;

                //int a = 3;

                //foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Monday")) //menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Monday")
                //{
                //    for (var b = a; b < menulength + 3; b++)
                //    {
                //        string product = dataCells[b, 0].StringValue.ToString();
                //        string window = "(none)";
                //        if (lstCol == 2)
                //            window = dataCells[b, 1].StringValue.ToString();
                //        string it = item.Field<string>("PRODUCTDESC");
                //        string wd = item.Field<string>("WINDOWNAME");
                //        if (it != product || wd != window) continue;
                //        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                //        dataCells[b, lstCol].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));


                //        if (item.Field<int>("ItemSpicy") > 0)
                //        {
                //            dataSheet.Pictures.Add(b, lstCol, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                //            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol);
                //        }


                //        //为菜上色
                //        //dataStyle.Pattern = BackgroundType.Solid;
                //        //dataStyle.ForegroundColor = System.Drawing.ColorTranslator.FromHtml(item.Field<string>("ItemColor").ToString().Substring(0, 1) == "#" ? item.Field<string>("ItemColor").ToString() : "#FFFFFF");//System.Drawing.Color.Yellow;
                //        //dataCells[b, lstCol].SetStyle(dataStyle);
                //        a = b;
                //        a++;
                //        break;
                //    }
                //}

                //a = 3;
                //foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Tuesday"))
                //{
                //    for (var b = a; b < menulength + 3; b++)
                //    {
                //        string product = dataCells[b, 0].StringValue.ToString();
                //        string window = "(none)";
                //        if (lstCol == 2)
                //            window = dataCells[b, 1].StringValue.ToString();
                //        if (item.Field<string>("PRODUCTDESC") != product || item.Field<string>("WINDOWNAME") != window) continue;
                //        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                //        dataCells[b, lstCol + 1].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));

                //        //菜色辣度
                //        if (item.Field<int>("ItemSpicy") > 0)
                //        {
                //            dataSheet.Pictures.Add(b, lstCol + 1, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                //            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol + 1);
                //        }


                //        a = b;
                //        a++;
                //        break;
                //    }
                //}

                //a = 3;
                //foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Wednesday"))
                //{
                //    for (var b = a; b < menulength + 3; b++)
                //    {

                //        string product = dataCells[b, 0].StringValue.ToString();
                //        string window = "(none)";
                //        if (lstCol == 2)
                //            window = dataCells[b, 1].StringValue.ToString();
                //        if (item.Field<string>("PRODUCTDESC") != product || item.Field<string>("WINDOWNAME") != window) continue;
                //        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                //        dataCells[b, lstCol + 2].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));

                //        //菜色辣度
                //        if (item.Field<int>("ItemSpicy") > 0)
                //        {
                //            dataSheet.Pictures.Add(b, lstCol + 2, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                //            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol + 2);
                //        }
                //        a = b;
                //        a++;
                //        break;
                //    }
                //}

                //a = 3;
                //foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Thursday"))
                //{
                //    for (var b = a; b < menulength + 3; b++)
                //    {
                //        string product = dataCells[b, 0].StringValue.ToString();
                //        string window = "(none)";
                //        if (lstCol == 2)
                //            window = dataCells[b, 1].StringValue.ToString();
                //        if (item.Field<string>("PRODUCTDESC") != product || item.Field<string>("WINDOWNAME") != window) continue;
                //        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                //        dataCells[b, lstCol + 3].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));

                //        //菜色辣度
                //        if (item.Field<int>("ItemSpicy") > 0)
                //        {
                //            dataSheet.Pictures.Add(b, lstCol + 3, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                //            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol + 3);
                //        }

                //        a = b;
                //        a++;
                //        break;
                //    }
                //}

                //a = 3;
                //foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Friday"))
                //{
                //    for (var b = a; b < menulength + 3; b++)
                //    {
                //        string product = dataCells[b, 0].StringValue.ToString();
                //        string window = "(none)";
                //        if (lstCol == 2)
                //            window = dataCells[b, 1].StringValue.ToString();
                //        if (item.Field<string>("PRODUCTDESC") != product || item.Field<string>("WINDOWNAME") != window) continue;
                //        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                //        dataCells[b, lstCol + 4].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));

                //        //菜色辣度
                //        if (item.Field<int>("ItemSpicy") > 0)
                //        {
                //            dataSheet.Pictures.Add(b, lstCol + 4, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                //            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol + 4);
                //        }

                //        a = b;
                //        a++;
                //        break;
                //    }
                //}

                //a = 3;
                //foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Saturday"))
                //{
                //    for (var b = a; b < menulength + 3; b++)
                //    {
                //        string product = dataCells[b, 0].StringValue.ToString();
                //        string window = "(none)";
                //        if (lstCol == 2)
                //            window = dataCells[b, 1].StringValue.ToString();
                //        if (item.Field<string>("PRODUCTDESC") != product || item.Field<string>("WINDOWNAME") != window) continue;
                //        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                //        dataCells[b, lstCol + 5].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));

                //        //菜色辣度
                //        if (item.Field<int>("ItemSpicy") > 0)
                //        {
                //            dataSheet.Pictures.Add(b, lstCol + 5, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                //            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol + 5);
                //        }

                //        a = b;
                //        a++;
                //        break;
                //    }
                //}

                //a = 3;
                //foreach (var item in menu.AsEnumerable().Where(g => g.Field<string>("WEEKDAY") == "Sunday"))
                //{
                //    for (var b = a; b < menulength + 3; b++)
                //    {
                //        string product = dataCells[b, 0].StringValue.ToString();
                //        string window = "(none)";
                //        if (lstCol == 2)
                //            window = dataCells[b, 1].StringValue.ToString();
                //        if (item.Field<string>("PRODUCTDESC") != product || item.Field<string>("WINDOWNAME") != window) continue;
                //        dataCells[b, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                //        dataCells[b, lstCol + 6].PutValue(item.Field<string>("ItemName_ZH") + ' ' + item.Field<string>("ItemName_EN"));

                //        //菜色辣度
                //        if (item.Field<int>("ItemSpicy") > 0)
                //        {
                //            dataSheet.Pictures.Add(b, lstCol + 6, (Stream)peppers.GetValue(item.Field<int>("ItemSpicy")), 5, 5);

                //            dataSheet.Pictures[dataSheet.Pictures.Count - 1].AlignTopRightCorner(b, lstCol + 6);
                //        }
                //        a = b;
                //        a++;
                //        break;
                //    }
                //}

                //for (int c = 3; c < menulength + 3; c++)
                //{
                //    sidepanelStyle2.ForegroundColor = System.Drawing.Color.Yellow;
                //    sidepanelStyle.ForegroundColor = System.Drawing.Color.Yellow;
                //    sidepanelStyle2.Font.Color = System.Drawing.Color.Yellow;
                //    sidepanelStyle.Font.Color = System.Drawing.Color.Yellow;

                //    var cur = dataCells[c, 0].StringValue.ToString();
                //    var nex = dataCells[c + 1, 0].StringValue.ToString();

                //    if (cur == nex)
                //    {
                //        //设置字体颜色和背景色
                //        dataCells[c + 1, 0].SetStyle(sidepanelStyle2);
                //    }
                //    else
                //    {
                //        if (c + 1 != menulength + 3)
                //        {
                //            sidepanelStyle.Font.Color = System.Drawing.Color.Black;
                //            dataCells[c + 1, 0].SetStyle(sidepanelStyle);
                //        }
                //    }
                //    ////如果单元格无值，则背景色为白色
                //    //if(string.IsNullOrWhiteSpace(dataCells[c, lstCol].StringValue.ToString()))
                //    //{
                //    //    dataStyle.ForegroundColor = System.Drawing.Color.White;
                //    //    dataCells[c, lstCol].SetStyle(dataStyle);
                //    //}

                //    dataCells[c, lstCol].SetStyle(dataStyle);
                //    dataCells[c, lstCol + 1].SetStyle(dataStyle);
                //    dataCells[c, lstCol + 2].SetStyle(dataStyle);
                //    dataCells[c, lstCol + 3].SetStyle(dataStyle);
                //    dataCells[c, lstCol + 4].SetStyle(dataStyle);
                //    dataCells[c, lstCol + 5].SetStyle(dataStyle);
                //    dataCells[c, lstCol + 6].SetStyle(dataStyle);
                //}

                //int bgn = 3;
                //int egn = 1;
                //for (int c = 3; c < menulength + 3; c++)
                //{
                //    var cur = dataCells[c, 0].StringValue.ToString();
                //    var nex = dataCells[c + 1, 0].StringValue.ToString();

                //    if (cur == nex)
                //    {
                //        egn++;
                //        continue;
                //    }
                //    else if (cur != nex)
                //    {
                //        dataCells.Merge(bgn, 0, egn, 1);
                //        bgn = c + 1;
                //        egn = 1;
                //    }
                //}

                //int bgn2 = 3;
                //int egn2 = 1;
                //for (int c = 3; c < menulength + 3; c++)
                //{
                //    var cur = dataCells[c, 1].StringValue.ToString();
                //    var nex = dataCells[c + 1, 1].StringValue.ToString();

                //    if (cur == nex)
                //    {
                //        egn2++;
                //        continue;
                //    }
                //    else if (cur != nex)
                //    {
                //        dataCells.Merge(bgn2, 1, egn2, 1);
                //        bgn2 = c + 1;
                //        egn2 = 1;
                //    }
                //}
                #endregion

                dataSheet.AutoFitRows();
                //dataCells.SetRowHeight(0, 60);
                //dataCells.SetColumnWidth(lstCol, (int)dataCells.GetColumnWidth(lstCol) + 10);
                //dataCells.SetColumnWidth(lstCol + 1, (int)dataCells.GetColumnWidth(lstCol + 1) + 10);
                //dataCells.SetColumnWidth(lstCol + 2, (int)dataCells.GetColumnWidth(lstCol + 2) + 10);
                //dataCells.SetColumnWidth(lstCol + 3, (int)dataCells.GetColumnWidth(lstCol + 3) + 10);
                //dataCells.SetColumnWidth(lstCol + 4, (int)dataCells.GetColumnWidth(lstCol + 4) + 10);
                //dataCells.SetColumnWidth(lstCol + 5, (int)dataCells.GetColumnWidth(lstCol + 5) + 10);
                //dataCells.SetColumnWidth(lstCol + 6, (int)dataCells.GetColumnWidth(lstCol + 6) + 10);

                return excel.SaveToStream();

                //DownloadExcelFile(excel.SaveToStream());
                //var excelName = (DateTime.Now.ToString("yyyy-MM-dd HH.mm.ss") + "WeeklyReport.xls").ToString();
                //excel.Save("C:/" + excelName);
                //System.Diagnostics.Process.Start("C:/" + excelName);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //下载Excel工艺单
        public static MemoryStream ExportExcelMenuProcess(Dictionary<string, string> keyDic)
        {
            try
            {
                string filter = "";
                if (!string.IsNullOrWhiteSpace(keyDic["windowName"]))
                    filter = " and isnull(M.windowGUID,'')<>'' and isnull(S.windowGuid,'')<>'' ";
                else
                    filter = " and isnull(M.windowGUID,'')='' and isnull(S.windowGuid,'')='' ";


                string sql = string.Format("SELECT convert(int,row_number() OVER (partition by L.PRODUCTDESC ORDER BY L.PRODUCTDESC)) 'Index', M.COSTCENTERCODE,isnull(S.WINDOWNAME,'(none)') WINDOWNAME,CONVERT(VARCHAR(100), M.REQUIREDDATE, 20) AS REQUIREDDATE, L.PRODUCTDESC, M.ITEMGUID, M.ITEMCODE, M.ITEMTYPE, M.ITEMNAME_ZH, M.ItemName_EN, "
                    + "isnull(Q.Step,0) Step,Q.Content,M.ITEMNAME_EN, convert(decimal(18,0),M.REQUIREDQTY) REQUIREDQTY, M.ITEMCOST, M.OTHERCOST, M.ITEMUNITCODE, M.HEADGUID, M.PRODUCTGUID, M.PURCHASEPOLICY, CONVERT(VARCHAR(100), M.CREATETIME, 20) AS CREATETIME, "
                    + "M.CREATEUSER, CONVERT(VARCHAR(100), M.DELETETIME, 20) AS DELETETIME, M.DELETEUSER,(CASE WHEN M.LINKID = 0 THEN M.ID ELSE M.LINKID END) AS LINKID, "
                    + "datename(weekday, CONVERT(VARCHAR(100), M.REQUIREDDATE, 20)) WEEKDAY FROM MENUORDERHEAD (nolock) AS M INNER JOIN SALESORDERITEM (nolock) AS L ON M.SOITEMGUID = L.ITEMGUID AND L.STATUS <> '0' "
                    //+ "INNER JOIN PRODUCTTYPEDATA K ON K.id=L.ProductCode LEFT JOIN (select distinct WindowSort as Sort,windowGuid,WindowName,SOItemGuid from CCWindowMeals where deletetime is null) S ON S.WINDOWGUID=M.WindowGUID and M.SOITEMGUID=S.SOItemGuid "
                    + "LEFT JOIN PRODUCTTYPEDATA K ON K.id=L.ProductCode LEFT JOIN (select distinct WindowSort as Sort,windowGuid,WindowName,SOItemGuid from CCWindowMeals where deletetime is null) S ON S.WINDOWGUID=M.WindowGUID and M.SOITEMGUID=S.SOItemGuid "
                    + "LEFT JOIN TBLITEMPROCESS (nolock) Q on Q.ItemGUID = M.ItemGUID and Q.DeleteTime is null "
                    + "LEFT JOIN TBLITEM P ON P.ItemCode=M.ITEMCODE "
                    + "WHERE P.DELETETIME IS NULL and M.COSTCENTERCODE = '{0}' and Q.Type='cookway' "
                    + "AND M.DELETEUSER IS NULL AND M.REQUIREDDATE BETWEEN "
                    + "convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{1}'), 0)), 23) AND convert(varchar(10), (DATEADD(wk, DATEDIFF(wk, 0, '{1}'), 6)), 23) {2} "
                    + "order by L.ProductCode,L.PRODUCTDESC,S.Sort,M.REQUIREDDATE,S.WINDOWNAME,M.ITEMCODE,Q.Step", keyDic["costCenterCode"], keyDic["startDate"], filter);

                var menuProcessdata = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql);
                var firstCol = menuProcessdata.AsEnumerable().Select(dr => dr.Field<string>("WINDOWNAME")).FirstOrDefault();

                int lstCol = 2;
                if (firstCol == null) lstCol = 1;
                else if (firstCol.Contains("none")) lstCol = 1;

                Workbook excel = new Workbook();

                Style titleStyle = excel.Styles[excel.Styles.Add()];
                titleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                titleStyle.Font.Size = 15;
                titleStyle.Font.IsBold = true;

                Style tabletitleStyle = excel.Styles[excel.Styles.Add()];
                tabletitleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                tabletitleStyle.Font.Size = 10;
                tabletitleStyle.Font.IsBold = true;
                tabletitleStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                tabletitleStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                tabletitleStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                tabletitleStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);

                Style cellStyle = excel.Styles[excel.Styles.Add()];
                cellStyle.Font.Size = 10;
                cellStyle.Font.Color = System.Drawing.Color.Black;
                cellStyle.Pattern = BackgroundType.Solid;
                cellStyle.ForegroundColor = System.Drawing.Color.White;
                cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellStyle.IsTextWrapped = true;

                Style cellStyleWhite = excel.Styles[excel.Styles.Add()];
                cellStyleWhite.Font.Size = 10;
                cellStyleWhite.Font.Color = System.Drawing.Color.White;
                cellStyleWhite.Pattern = BackgroundType.Solid;
                cellStyleWhite.ForegroundColor = System.Drawing.Color.White;
                cellStyleWhite.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellStyleWhite.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellStyleWhite.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.White);

                Style cellProcess = excel.Styles[excel.Styles.Add()];
                cellProcess.Font.Size = 10;
                cellProcess.Font.Color = System.Drawing.Color.Black;
                cellProcess.Pattern = BackgroundType.Solid;
                cellProcess.ForegroundColor = System.Drawing.Color.White;
                cellProcess.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellProcess.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellProcess.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellProcess.IsTextWrapped = true;

                Style cellLastRow = excel.Styles[excel.Styles.Add()];
                cellLastRow.Font.Size = 10;
                cellLastRow.Font.Color = System.Drawing.Color.White;
                cellLastRow.Pattern = BackgroundType.Solid;
                cellLastRow.ForegroundColor = System.Drawing.Color.White;
                cellLastRow.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellLastRow.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellLastRow.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellLastRow.IsTextWrapped = true;

                string n = ""; string wd = "";
                for (int i = 0; i < 7; i++)
                {
                    if (i == 0) { n = "一"; wd = "Monday"; }
                    else if (i == 1) { n = "二"; wd = "Tuesday"; }
                    else if (i == 2) { n = "三"; wd = "Wednesday"; }
                    else if (i == 3) { n = "四"; wd = "Thursday"; }
                    else if (i == 4) { n = "五"; wd = "Friday"; }
                    else if (i == 5) { n = "六"; wd = "Saturday"; }
                    else if (i == 6) { n = "日"; wd = "Sunday"; }
                    if (i == 0) excel.Worksheets[i].Name = "周" + n;
                    else if (i > 0) excel.Worksheets.Add("周" + n);

                    excel.Worksheets[i].Cells.Merge(0, 0, 2, 4 + lstCol);
                    excel.Worksheets[i].Cells[0, 0].PutValue(keyDic["costCenterCode"] + "菜谱工艺单" + ' ' + "日期:" + DateTime.Parse(keyDic["startDate"]).AddDays(i).ToString("yyyy-MM-dd") + ' ' + "周" + n);
                    excel.Worksheets[i].Cells[0, 0].SetStyle(titleStyle);
                    excel.Worksheets[i].Cells[3, 0].PutValue("分类");
                    if (lstCol == 2) excel.Worksheets[i].Cells[3, 1].PutValue("窗口");
                    excel.Worksheets[i].Cells[3, lstCol].PutValue("菜名");
                    excel.Worksheets[i].Cells[3, lstCol + 1].PutValue("份数");
                    excel.Worksheets[i].Cells[3, lstCol + 2].PutValue("加工步骤");
                    excel.Worksheets[i].Cells[3, lstCol + 3].PutValue("备注");

                    excel.Worksheets[i].Cells[3, 0].SetStyle(tabletitleStyle);
                    if (lstCol == 2) excel.Worksheets[i].Cells[3, 1].SetStyle(tabletitleStyle);
                    excel.Worksheets[i].Cells[3, lstCol].SetStyle(tabletitleStyle);
                    excel.Worksheets[i].Cells[3, lstCol + 1].SetStyle(tabletitleStyle);
                    excel.Worksheets[i].Cells[3, lstCol + 2].SetStyle(tabletitleStyle);
                    excel.Worksheets[i].Cells[3, lstCol + 3].SetStyle(tabletitleStyle);

                    int row = 3;
                    foreach (var item in menuProcessdata.AsEnumerable().Where(dr => dr.Field<string>("WEEKDAY") == wd))
                    {

                        row++;
                        excel.Worksheets[i].Cells[row, 0].PutValue(item.Field<string>("PRODUCTDESC"));
                        if (lstCol == 2) excel.Worksheets[i].Cells[row, 1].PutValue(item.Field<string>("WINDOWNAME"));
                        excel.Worksheets[i].Cells[row, lstCol].PutValue(item.Field<string>("ITEMNAME_ZH") + ' ' + item.Field<string>("ItemName_EN"));
                        excel.Worksheets[i].Cells[row, lstCol + 1].PutValue(item.Field<decimal>("REQUIREDQTY") + " " + item.Field<string>("ITEMUNITCODE"));
                        excel.Worksheets[i].Cells[row, lstCol + 2].PutValue((item.Field<int>("Step").ToString() == "0" ? "" : item.Field<int>("Step").ToString()) + (item.Field<int>("Step").ToString() == "0" ? "" : "、")
                        + item.Field<string>("Content"));

                        if (item.Field<int>("Step").ToString() != "1" && item.Field<int>("Step").ToString() != "0")
                        {
                            excel.Worksheets[i].Cells[row, 0].SetStyle(cellStyleWhite);
                            if (lstCol == 2) excel.Worksheets[i].Cells[row, 1].SetStyle(cellStyleWhite);
                            excel.Worksheets[i].Cells[row, lstCol].SetStyle(cellStyleWhite);
                            excel.Worksheets[i].Cells[row, lstCol + 1].SetStyle(cellStyleWhite);
                            excel.Worksheets[i].Cells[row, lstCol + 2].SetStyle(cellProcess);
                            excel.Worksheets[i].Cells[row, lstCol + 3].SetStyle(cellProcess);
                        }
                        else
                        {
                            if (excel.Worksheets[i].Cells[row, 0].StringValue.ToString() == excel.Worksheets[i].Cells[row - 1, 0].StringValue.ToString())
                                excel.Worksheets[i].Cells[row, 0].SetStyle(cellStyleWhite);
                            else
                                excel.Worksheets[i].Cells[row, 0].SetStyle(cellStyle);
                            if (lstCol == 2)
                            {
                                if (excel.Worksheets[i].Cells[row, 1].StringValue.ToString() == excel.Worksheets[i].Cells[row - 1, 1].StringValue.ToString())
                                    excel.Worksheets[i].Cells[row, 1].SetStyle(cellStyleWhite);
                                else
                                    excel.Worksheets[i].Cells[row, 1].SetStyle(cellStyle);
                            }
                            excel.Worksheets[i].Cells[row, lstCol].SetStyle(cellStyle);
                            excel.Worksheets[i].Cells[row, lstCol + 1].SetStyle(cellStyle);
                            excel.Worksheets[i].Cells[row, lstCol + 2].SetStyle(cellProcess);
                            excel.Worksheets[i].Cells[row, lstCol + 3].SetStyle(cellProcess);
                        }
                    }

                    var s = menuProcessdata.AsEnumerable().Where(dr => dr.Field<string>("WEEKDAY") == wd).ToList().ToList();
                    if (menuProcessdata.AsEnumerable().Where(dr => dr.Field<string>("WEEKDAY") == wd).ToList().Count() == 0)
                    {
                        excel.Worksheets[i].Cells[row + 1, 0].PutValue("无数据 (No Data)");
                        continue;
                    }
                    if (row > 1 && excel.Worksheets[i].Cells[row - 1, 0].StringValue.ToString() == excel.Worksheets[i].Cells[row, 0].StringValue.ToString())
                        excel.Worksheets[i].Cells[row, 0].SetStyle(cellLastRow);
                    else
                        excel.Worksheets[i].Cells[row, 0].SetStyle(cellProcess);
                    if (lstCol == 2)
                    {
                        if (row > 1 && excel.Worksheets[i].Cells[row - 1, 1].StringValue.ToString() == excel.Worksheets[i].Cells[row, 1].StringValue.ToString())
                            excel.Worksheets[i].Cells[row, 1].SetStyle(cellLastRow);
                        else
                            excel.Worksheets[i].Cells[row, 1].SetStyle(cellProcess);
                    }
                    if (row > 1 && excel.Worksheets[i].Cells[row - 1, 1].StringValue.ToString() == excel.Worksheets[i].Cells[row, 1].StringValue.ToString())
                        excel.Worksheets[i].Cells[row, lstCol].SetStyle(cellLastRow);
                    else
                        excel.Worksheets[i].Cells[row, lstCol].SetStyle(cellProcess);

                    if (row > 1 && excel.Worksheets[i].Cells[row - 1, 1].StringValue.ToString() == excel.Worksheets[i].Cells[row, 1].StringValue.ToString())
                        excel.Worksheets[i].Cells[row, lstCol + 1].SetStyle(cellLastRow);
                    else
                        excel.Worksheets[i].Cells[row, lstCol + 1].SetStyle(cellProcess);

                    excel.Worksheets[i].Cells.SetColumnWidth(0, 20);
                    if (lstCol == 2) excel.Worksheets[i].Cells.SetColumnWidth(1, 20);
                    excel.Worksheets[i].Cells.SetColumnWidth(lstCol, 30);
                    excel.Worksheets[i].Cells.SetColumnWidth(lstCol + 1, 8);
                    excel.Worksheets[i].Cells.SetColumnWidth(lstCol + 2, 130);
                    excel.Worksheets[i].Cells.SetColumnWidth(lstCol + 3, 80);

                    excel.Worksheets[i].AutoFitColumns();
                }

                return excel.SaveToStream();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //下载微信充值报告
        public static MemoryStream WechatRechargeReport(Dictionary<string, string> keyDic)
        {
            try
            {
                string filter = "";
                string filterDBCC = "";

                if (!string.IsNullOrWhiteSpace(keyDic["startDate"]) && !string.IsNullOrWhiteSpace(keyDic["endDate"]))
                    filter = string.Format(" and (cast(left(a1.time_end, 8) as date)) between '{0}' and '{1}' ",
                        DateTime.Parse(keyDic["startDate"]).ToString("yyyy-MM-dd"), DateTime.Parse(keyDic["endDate"]).ToString("yyyy-MM-dd"));

                if (!string.IsNullOrWhiteSpace(keyDic["companyCode"]))
                    filterDBCC = string.Format(" and a3.DBName = '{0}'", keyDic["companyCode"]);
                else
                    filterDBCC = string.Format(" and a2.CostcenterCode = '{0}'", keyDic["costCenterCode"]);

                string sql = string.Format("select distinct a3.DBName,a2.CostcenterCode,a1.cardId,a4.UserName, "
                    + "(cast(left(a1.time_end,8) as date)) as endDate,sum(isnull(a1.total_fee,0)*1.0 /100) value "
                    + "from WxOrder a1,WxUserMast a2, CCMast a3,wxUserCard a4 "
                    + "where a1.NotifyTime is not null and a1.return_code = 'SUCCESS' "
                    + "and a1.result_code = 'SUCCESS' and a1.openid = a2.WechatId and a4.WechatId=a1.openid and a1.CardId=a4.CardId "
                    + "and a3.CostCenterCode = a2.CostcenterCode " + filterDBCC + " and a2.DeleteTime is null "
                    + filter
                    + "group by a3.DBName,a2.CostcenterCode,a1.CardId,a4.UserName,(cast(left(a1.time_end, 8) as date)) order by a2.CostcenterCode,cast(left(a1.time_end,8) as date),a1.cardId ", keyDic["companyCode"]);

                //and a4.DeleteTime is null

                var data = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql);

                Workbook excel = new Workbook();
                if (data == null || data.Rows.Count == 0)
                {
                    excel.Worksheets[0].Cells[0, 0].PutValue("No Data");
                }

                else
                {
                    var siteList = data.AsEnumerable().
                        GroupBy(dr => dr.Field<string>("costCenterCode")).
                        Select(r => new
                        {
                            costCenterCode = r.Key,
                            value = data.AsEnumerable().Where(dr => dr.Field<string>("costCenterCode") == r.Key).Sum(x => x.Field<decimal>("value"))
                        }
                    ).Distinct().ToList();


                    Style titleStyle = excel.Styles[excel.Styles.Add()];
                    titleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                    titleStyle.Font.Size = 10;
                    titleStyle.Font.IsBold = true;

                    Style numberStyle = excel.Styles[excel.Styles.Add()];
                    numberStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                    numberStyle.Custom = "#,###.00";

                    excel.Worksheets[0].Name = "汇总";
                    excel.Worksheets[0].Cells[0, 0].PutValue("营运点");
                    excel.Worksheets[0].Cells[0, 0].SetStyle(titleStyle);

                    excel.Worksheets[0].Cells[0, 1].PutValue("充值金额");
                    excel.Worksheets[0].Cells[0, 1].SetStyle(titleStyle);


                    int row = 0;
                    foreach (var item in siteList)
                    {
                        row++;
                        excel.Worksheets[0].Cells[row, 0].PutValue(item.costCenterCode);
                        excel.Worksheets[0].Cells[row, 1].PutValue(item.value);
                        excel.Worksheets[0].Cells[row, 1].SetStyle(numberStyle);

                        excel.Worksheets.Add(item.costCenterCode);
                        excel.Worksheets[row].Cells[0, 0].PutValue("营运点");
                        excel.Worksheets[row].Cells[0, 0].SetStyle(titleStyle);

                        excel.Worksheets[row].Cells[0, 1].PutValue("卡号");
                        excel.Worksheets[row].Cells[0, 1].SetStyle(titleStyle);

                        excel.Worksheets[row].Cells[0, 2].PutValue("用户");
                        excel.Worksheets[row].Cells[0, 2].SetStyle(titleStyle);

                        excel.Worksheets[row].Cells[0, 3].PutValue("充值时间");
                        excel.Worksheets[row].Cells[0, 3].SetStyle(titleStyle);

                        excel.Worksheets[row].Cells[0, 4].PutValue("充值金额");
                        excel.Worksheets[row].Cells[0, 4].SetStyle(titleStyle);


                        var detailData = data.AsEnumerable().Where(dr => dr.Field<string>("CostcenterCode") == item.costCenterCode).ToList();


                        for (int i = 0; i < detailData.Count; i++)
                        {
                            var dr = detailData[i];
                            excel.Worksheets[row].Cells[i + 1, 0].PutValue(dr.Field<string>("CostcenterCode"));
                            excel.Worksheets[row].Cells[i + 1, 1].PutValue(dr.Field<string>("cardId"));
                            excel.Worksheets[row].Cells[i + 1, 2].PutValue(dr.Field<string>("UserName"));
                            excel.Worksheets[row].Cells[i + 1, 3].PutValue(dr.Field<DateTime>("endDate").ToString("yyyy-MM-dd"));
                            excel.Worksheets[row].Cells[i + 1, 4].PutValue(dr.Field<decimal>("value"));
                            excel.Worksheets[row].Cells[i + 1, 4].SetStyle(numberStyle);
                        }

                        excel.Worksheets[row].AutoFitColumns();
                    }
                }
                excel.Worksheets[0].AutoFitColumns();

                return excel.SaveToStream();
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public static MemoryStream SiteSurveyReport(Dictionary<string, string> keyDic)
        {
            try
            {

                List<SiteSurvey> list = new Aden.DAL.SiteDIY.SiteDIYFactory().SiteSurveyDetails(keyDic["siteGuid"], "");
                if (list == null || !list.Any())
                    return null;

                Workbook excel = new Workbook();
                Worksheet dataSheet = excel.Worksheets[0];
                dataSheet.Name = "餐厅满意度评估表";
                Cells dataCells = dataSheet.Cells;

                Style titleStyle = excel.Styles[excel.Styles.Add()];
                titleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                titleStyle.Font.Size = 10;
                titleStyle.Font.IsBold = true;
                titleStyle.Pattern = BackgroundType.Solid;
                titleStyle.ForegroundColor = System.Drawing.Color.LightSkyBlue;

                Style cellStyle = excel.Styles[excel.Styles.Add()];
                cellStyle.Font.Size = 10;
                cellStyle.Font.Color = System.Drawing.Color.Black;
                cellStyle.Pattern = BackgroundType.Solid;
                cellStyle.ForegroundColor = System.Drawing.Color.White;
                cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellStyle.IsTextWrapped = true;

                #region 表头

                dataCells[0, 0].PutValue("用户代码");
                dataCells[0, 0].SetStyle(titleStyle);
                dataCells[0, 1].PutValue("问卷提交时间");
                dataCells[0, 1].SetStyle(titleStyle);

                List<SiteSurvey> SurveyDetails = list;
                List<dynamic> surveyItems = new List<dynamic>();
                foreach (SiteSurvey row in SurveyDetails)
                {
                    if (row.surveyDetails.Count > 0)
                    {
                        var details = row.surveyDetails;
                        foreach (SiteSurvey item in details)
                        {

                            SiteSurvey obj = new SiteSurvey();
                            obj.surveyItem = item.surveyItem;
                            obj.createUserID = item.createUserID;
                            obj.createUserName = item.createUserName;
                            obj.department = item.department;
                            obj.createTime = item.createTime;
                            obj.userAnswer = item.userAnswer;
                            obj.sort = item.sort;
                            surveyItems.Add(obj);
                        }
                    }

                }

                if (surveyItems == null || !surveyItems.Any())
                    return null;

                //问卷提问项
                List<SiteSurvey> ItemsTemp = new List<SiteSurvey>();
                List<string> Items = new List<string>();
                for (int i = 0; i < surveyItems.Count; i++)
                {
                    SiteSurvey obj = new SiteSurvey();
                    obj.surveyItem = surveyItems[i].surveyItem;
                    obj.sort = surveyItems[i].sort;
                    ItemsTemp.Add(obj);
                }

                if (ItemsTemp != null && ItemsTemp.Any())
                    Items = ItemsTemp.OrderBy(dr => dr.sort).Select(ds => ds.surveyItem).Distinct().ToList<string>();//问卷提问项

                int a = 1;
                //设置表头-问卷提问项
                foreach (var item in Items)
                {
                    a++;
                    dataCells[0, a].PutValue(item);
                    dataCells[0, a].SetStyle(titleStyle);
                }

                List<SiteSurvey> userInfo = new List<SiteSurvey>();

                #endregion

                int r = 0;

                foreach (var item in list)
                {
                    int j = 0;
                    r++;
                    dataCells[r, j].PutValue(item.createUserID);
                    dataCells[r, j++].SetStyle(cellStyle);
                    dataCells[r, j].PutValue(item.createTime.ToString("yyyy-MM-dd HH:mm:ss"));
                    dataCells[r, j++].SetStyle(cellStyle);

                    if (item.surveyDetails != null && item.surveyDetails.Any())
                    {
                        dynamic row = item.surveyDetails;
                        for (int s = 0; s < row.Count; s++)
                        {
                            for (int x = j; x <= j + surveyItems.Count; x++)
                            {
                                if (row[s].surveyItem == dataCells[0, x].StringValue)
                                {
                                    dataCells[r, x].PutValue(row[s].userAnswer);
                                    dataCells[r, x].SetStyle(cellStyle);
                                }
                            }

                        }
                    }
                }

                int maxCol = 2 + Items.Count;
                int maxRow = r;

                for (int rw = 1; rw < maxRow + 1; rw++)
                {

                    for (int col = 0; col < maxCol; col++)
                    {

                        dataCells[rw, col].SetStyle(cellStyle);
                    }
                }

                dataSheet.AutoFitRows();
                return excel.SaveToStream();

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        /// <summary>
        /// 导出价目表缺失的原物料清单
        /// </summary>
        /// <param name="keyDic"></param>
        /// <returns></returns>
        public static MemoryStream InvalidPurchaseItems(Dictionary<string, string> keyDic)
        {
            try
            {
                string sqlRecipe = string.Format("SELECT DISTINCT A2.ITEMCODE AS PRODUCTCODE,A2.ITEMNAME_ZH AS PRODUCTNAME, "
                    + " A1.ITEMCODE,A1.ITEMNAME,A1.OTHERNAME,A1.SUPPLIERCODE,A1.ITEMCOST "
                    // + " ,A1.ITEMPURCHASEUNIT,A1.CONVERSIONRATE,A1.CONVERSIONUNIT "
                    + " FROM TBLITEMBOM (NOLOCK) A1,TBLITEM (NOLOCK) A2 WHERE A1.DELETETIME IS NULL AND A2.DELETETIME IS NULL AND A2.GUID = A1.PRODUCTGUID "
                    + " AND A1.PURCHASEPOLICY ='ONDEMAND' AND A1.COSTCENTERCODE='{0}' ORDER BY A1.ITEMCODE ", keyDic["costCenterCode"]);

                string sqlPriceList = string.Format("SELECT A1.ITEMCODE,A1.ITEMNAME_ZH AS ITEMNAME,A1.Supplier_Code AS SUPPLIERCODE, "
                    + "A1.PRICE AS ITEMPURCHASEPRICE, A1.UNITCODE AS ITEMPURCHASEUNIT, A1.CONVERSIONRATE, A1.CONVERSIONUNIT,A1.VALIDFROM,ISNULL(A1.VALIDTO,'2099-12-31') VALIDTO "
                    + "FROM [DBO].[DM_D_ERP_PURCHASEAGREEMENT_NEW] (NOLOCK) A1,[DBO].[CCLASSSUPPLIER] (NOLOCK) A2 "
                    + "WHERE A1.Division COLLATE CHINESE_PRC_CI_AS = A2.DBNAME AND A1.Supplier_Code COLLATE CHINESE_PRC_CI_AS = A2.SUPPLIERCODE "
                    + "AND A2.COSTCENTERCODE = '{0}' ORDER BY A1.ITEMCODE,A1.VALIDFROM ",
                    keyDic["costCenterCode"]);

                var dataItemBOM = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sqlRecipe);

                var dataPurchaseList = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sqlPriceList);
                //开始日期
                DateTime sDate = Convert.ToDateTime(keyDic["startDate"]);
                //结束日期
                DateTime eDate = Convert.ToDateTime(keyDic["endDate"]);

                var itemData = dataItemBOM.AsEnumerable().GroupBy(dr => dr.Field<string>("itemCode")).
                               GroupJoin(dataPurchaseList.AsEnumerable(),
                                mos => new { itemCode = mos.Key.ToString().Trim() },
                                nos => new { itemCode = nos.Field<string>("itemCode").ToString().Trim() },
                                (mos, nos) =>
                                {
                                    Dictionary<string, string> periodGap = new Dictionary<string, string>();
                                    if (nos.Count() == 0 || nos == null)
                                    {
                                        periodGap.Add("validFrom", "");
                                        periodGap.Add("validTo", "");
                                    }
                                    else if (Convert.ToDateTime(nos.FirstOrDefault().Field<string>("validFrom")) <= sDate &&
                                       Convert.ToDateTime(nos.LastOrDefault().Field<string>("validTo")) >= eDate)
                                    {
                                        for (int j = 1; j < nos.Count() - 1; j++)
                                        {
                                            if (Convert.ToDateTime(nos.AsEnumerable().ToArray()[j].Field<string>("validFrom")) > Convert.ToDateTime(nos.AsEnumerable().ToArray()[j - 1].Field<string>("validTo")).AddDays(1) ||
                                                Convert.ToDateTime(nos.AsEnumerable().ToArray()[j].Field<string>("validTo")) < Convert.ToDateTime(nos.AsEnumerable().ToArray()[j + 1].Field<string>("validFrom")).AddDays(-1))
                                            {
                                                periodGap.Add("validFrom", nos.AsEnumerable().ToArray()[j].Field<string>("validFrom"));
                                                periodGap.Add("validTo", nos.AsEnumerable().ToArray()[j].Field<string>("validTo"));
                                            };
                                        }
                                    }
                                    else
                                    {
                                        periodGap = new Dictionary<string, string>();
                                    }
                                    return new { mos, periodGap };
                                }).Where(dt => dt.periodGap.Count() != 0 && dt.periodGap != null);

                Workbook excel = new Workbook();
                Worksheet dataSheet = excel.Worksheets[0];
                dataSheet.Name = "Invalid Item List";
                Cells dataCells = dataSheet.Cells;

                Style titleStyle = excel.Styles[excel.Styles.Add()];
                titleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                titleStyle.Font.Size = 10;
                titleStyle.Font.IsBold = true;
                titleStyle.Pattern = BackgroundType.Solid;
                titleStyle.ForegroundColor = System.Drawing.Color.LightSkyBlue;

                Style cellStyle = excel.Styles[excel.Styles.Add()];
                cellStyle.Font.Size = 10;
                cellStyle.Font.Color = System.Drawing.Color.Black;
                cellStyle.Pattern = BackgroundType.Solid;
                cellStyle.ForegroundColor = System.Drawing.Color.White;
                cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
                cellStyle.IsTextWrapped = true;


                dataCells[0, 0].PutValue("Recipe Code");
                dataCells[0, 0].SetStyle(titleStyle);
                dataCells[0, 1].PutValue("Recipe Name");
                dataCells[0, 1].SetStyle(titleStyle);
                dataCells[0, 2].PutValue("Item Code");
                dataCells[0, 2].SetStyle(titleStyle);
                dataCells[0, 3].PutValue("Item Name");
                dataCells[0, 3].SetStyle(titleStyle);
                dataCells[0, 4].PutValue("Other Name");
                dataCells[0, 4].SetStyle(titleStyle);
                dataCells[0, 5].PutValue("Valid From");
                dataCells[0, 5].SetStyle(titleStyle);
                dataCells[0, 6].PutValue("Valid To");
                dataCells[0, 6].SetStyle(titleStyle);

                int r = 0;
                foreach (var item in itemData)
                {
                    r++;
                    for (int rc = 0; rc < item.mos.Count(); rc++)
                    {
                        r += rc;
                        int col = 0;
                        dataCells[r, col].PutValue(item.mos.AsEnumerable().ToArray()[rc].Field<string>("PRODUCTCODE"));
                        dataCells[r, col++].SetStyle(cellStyle);
                        dataCells[r, col].PutValue(item.mos.AsEnumerable().ToArray()[rc].Field<string>("PRODUCTNAME"));
                        dataCells[r, col++].SetStyle(cellStyle);
                        dataCells[r, col].PutValue(item.mos.AsEnumerable().ToArray()[rc].Field<string>("ITEMCODE"));
                        dataCells[r, col++].SetStyle(cellStyle);
                        dataCells[r, col].PutValue(item.mos.AsEnumerable().ToArray()[rc].Field<string>("ITEMNAME"));
                        dataCells[r, col++].SetStyle(cellStyle);
                        dataCells[r, col].PutValue(item.mos.AsEnumerable().ToArray()[rc].Field<string>("OTHERNAME"));
                        dataCells[r, col++].SetStyle(cellStyle);
                        dataCells[r, col].PutValue(item.periodGap["validFrom"]);
                        dataCells[r, col++].SetStyle(cellStyle);
                        dataCells[r, col].PutValue(item.periodGap["validTo"]);
                        dataCells[r, col++].SetStyle(cellStyle);
                    }
                }
                dataSheet.AutoFitRows();
                return excel.SaveToStream();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 获取报告参数列表
        /// </summary>
        /// <param name="menuMastID"></param>
        /// <returns></returns>
        private static List<SqlReportParam> GetReportParam(int menuMastID)
        {
            string sqlParam = "select a3.querytype,a3.id queryid,case when 'zh'='zh' then a1.MenuNameZh else a1.MenuNameEn end menuName,a2.controlType, "
                    + " case when 'zh' = 'zh' then a2.controlnameCN else a2.controlnameEN end controlName,a2.controlSize,a2.paramType,a2.tsqlValue, "
                    + " a2.fsqlValue,a2.defaultValue,a2.isNull,a2.sqlName,a2.expression1,a2.expression2, isnull(a2.hidden, 0) hidden,a2.isSqlParam "
                    + " from menumast a1 inner join sqlquery a3 on a1.sqlqid = a3.id left join sqlparam a2 on a1.sqlqid = a2.sqlid and a2.actived = 1 "
                    + " where a1.appType = 'Web' and a1.id = '{0}' order by a2.sort ";
            sqlParam = string.Format(sqlParam, menuMastID);
            List<SqlReportParam> SqlReportParam = SqlServerHelper.GetEntityList<SqlReportParam>(SqlServerHelper.salesorderConn(), sqlParam);
            return SqlReportParam;
        }

        private static string GetMenuMastList(string menuName)
        {
            string sql = "SELECT A.ID AS MENUMASTID,B.ID AS SQLID,A.MENUNAMEZH,A.MENUNAMEEN, B.SQL, B.TITLE,B.TEXTCOLUMN FROM MENUMAST (NOLOCK) A, "
                    + "SQLQUERY (NOLOCK) B WHERE A.SQLQId = B.ID AND A.MenuName = '{0}' ";
            sql = string.Format(sql, menuName);
            return sql;
        }

        /// <summary>
        /// 第 i 个Excel Sheet
        /// </summary>
        /// <param name="excel"></param>
        /// <param name="sheetNo"></param>
        /// <returns></returns>
        public static Worksheet ExcelTemplateStyle(Workbook excel, Worksheet dataSheet,int sheetNo, out Cells dataCells, 
            out Style ReportHeadStyle, out Style titleStyle, out Style subTitleStyle, out Style dataStyle,
            out Style qtyStyle,out Style TotalTitleStyle, out Style TotalDataStyle)
        {
            if (sheetNo > 0)
                dataSheet = excel.Worksheets.Add("Sheet"+sheetNo);
            
            dataCells = dataSheet.Cells;

            ReportHeadStyle = excel.Styles[excel.Styles.Add()];
            ReportHeadStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
            ReportHeadStyle.Font.Size = 10;
            ReportHeadStyle.Font.IsBold = true;
            ReportHeadStyle.HorizontalAlignment = TextAlignmentType.Center;
            ReportHeadStyle.VerticalAlignment = TextAlignmentType.Center;
            ReportHeadStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
            ReportHeadStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
            ReportHeadStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
            ReportHeadStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);

            titleStyle = excel.Styles[excel.Styles.Add()];
            titleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Left;
            titleStyle.Font.Size = 10;
            titleStyle.Font.IsBold = true;
            titleStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
            titleStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
            titleStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
            titleStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);

            subTitleStyle = excel.Styles[excel.Styles.Add()];
            subTitleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
            subTitleStyle.Font.Size = 10;
            subTitleStyle.Font.IsBold = false;
            subTitleStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Medium, System.Drawing.Color.Black);

            dataStyle = excel.Styles[excel.Styles.Add()];
            dataStyle.Custom = "#,##0.00";
            dataStyle.IsTextWrapped = true;

            qtyStyle = excel.Styles[excel.Styles.Add()];
            qtyStyle.Custom = "#,##0.0";

            TotalTitleStyle = excel.Styles[excel.Styles.Add()];
            TotalTitleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
            TotalTitleStyle.Font.Size = 10;
            TotalTitleStyle.Font.IsBold = true;

            TotalDataStyle = excel.Styles[excel.Styles.Add()];
            TotalDataStyle.Custom = "#,##0.00";
            TotalDataStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
            TotalDataStyle.Font.Size = 10;
            TotalDataStyle.Font.IsBold = true;
            TotalDataStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Double, System.Drawing.Color.Black);

            return dataSheet;
        }
        
        /// <summary>
        /// 自定义报告 - New Excel
        /// </summary>
        /// <param name="keyDic"></param>
        /// <returns></returns>
        public static MemoryStream GetCustomizedReport(Dictionary<string, string> keyDic)
        {
            try
            {
                Workbook excel = new Workbook();
                Worksheet dataSheet = excel.Worksheets[0];
                dataSheet = ExcelTemplateStyle(excel, dataSheet, 0, out Cells dataCells, 
                            out Style ReportHeadStyle, out Style titleStyle, out Style subTitleStyle, out Style dataStyle,
                            out Style qtyStyle, out Style TotalTitleStyle, out Style TotalDataStyle);

                // 获取主数据的Sql命令
                string sql = GetMenuMastList(keyDic["menuName"]);
                // 主数据列表
                List<MenuMast> MenuMastList = SqlServerHelper.GetEntityList<MenuMast>(SqlServerHelper.salesorderConn(), 
                    sql);
               
                if (MenuMastList == null || !MenuMastList.Any())
                    return null;
               
                List<SqlReportParam> ReportParam = GetReportParam(MenuMastList.FirstOrDefault().menuMastID);
                // 报告标题
                string title = MenuMastList.FirstOrDefault().title;
                // 获取报告数据的Sql命令
                string strMainData = MenuMastList.FirstOrDefault().sql;
                if(ReportParam != null || ReportParam.Count > 0)
                {
                    for(int u = 0; u < ReportParam.Count; u++)
                    {
                        string paramName = ReportParam[u].sqlName.Replace("[","").Replace("]", "");
                        if (!keyDic.ContainsKey(paramName))
                            continue;
                        if (!ReportParam[u].isSqlParam)
                            continue;
                        strMainData = strMainData.Replace(ReportParam[u].sqlName, keyDic[paramName]);
                    }
                }
                // 报告列表
                List<mic> MainData = SqlServerHelper.GetEntityList<mic>(SqlServerHelper.salesorderConn(), strMainData);
                
                if (MainData == null)
                {
                    dataSheet.AutoFitRows();
                    dataSheet.Protect(0, "123abc", "");
                    return excel.SaveToStream();
                }
                else
                {
                    // 项目点日报告
                    if (keyDic.ContainsKey("menuName") && keyDic["menuName"].ToLower() == "mic")
                        GetVNMICReport(keyDic, ReportParam, MainData, title, excel, dataSheet, 0);
                    else if(keyDic.ContainsKey("menuName") && keyDic["menuName"].ToLower().IndexOf("dailypotemplate")>-1)
                        GetVNDailyPO(keyDic, ReportParam, MainData, title, excel, dataSheet, 0,0,null);
                    else if(keyDic.ContainsKey("menuName") && keyDic["menuName"].ToLower().IndexOf("weeklypotemplate")>-1)
                    {
                        if (MainData != null && MainData.Any())
                        {
                            List<CatPO> dateList = MainData.GroupBy(dr => new { dr.orderDate,dr.supplierCode }).
                                Select(dr=> new CatPO()
                                {
                                    orderDate = dr.Key.orderDate,
                                    supplierCode= dr.Key.supplierCode
                                }).ToList();
                            GetVNDailyPO(keyDic, ReportParam, MainData,title, excel, dataSheet, 0, dateList.Count-1,dateList);
                        }
                    }
                }
                return excel.SaveToStream();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region GetVNDailyPO
        public static MemoryStream GetVNDailyPO(Dictionary<string, string> keyDic, List<SqlReportParam> ReportParam, List<mic> MainData, string title,
            Workbook excel, Worksheet dataSheet, int sheetNo, int maxSheetNo, List<CatPO> dateList)
        {
            var rawData = MainData;
            for(int number = sheetNo; number <= maxSheetNo; number++)
            {
                if (dateList != null && dateList.Count > 0)
                    rawData = MainData.
                        Where(dr => dr.orderDate == dateList[number].orderDate.ToString() 
                                   && dr.supplierCode == dateList[number].supplierCode.ToString()).ToList();

                dataSheet = ExcelTemplateStyle(excel,dataSheet, number, out Cells dataCells,
                        out Style ReportHeadStyle, out Style titleStyle, out Style subTitleStyle, out Style dataStyle,
                        out Style qtyStyle, out Style TotalTitleStyle, out Style TotalDataStyle);

                Style intStyle = excel.Styles[excel.Styles.Add()];
                intStyle.Custom = "0";
                intStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
                intStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);

                qtyStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
                qtyStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);

                subTitleStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
                subTitleStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
                subTitleStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
                subTitleStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
                subTitleStyle.Font.IsBold = true;

                Style reportFootStyle = excel.Styles[excel.Styles.Add()];
                reportFootStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
                reportFootStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
                reportFootStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
                reportFootStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
                reportFootStyle.HorizontalAlignment = TextAlignmentType.Center;
                reportFootStyle.IsTextWrapped = true;

                dataStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
                dataStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);

                #region 
                // 设置报告Logo
                if (keyDic.ContainsKey("pLogo") && !string.IsNullOrWhiteSpace(keyDic["pLogo"]))
                {

                    string logo = keyDic["pLogo"].ToString().Trim();
                    int col = dataCells[logo].Column;
                    int row = dataCells[logo].Row;

                    int rowspan = !keyDic.ContainsKey("rsLogo") ? 0 : int.Parse(keyDic["rsLogo"].ToString().Trim());
                    int colspan = !keyDic.ContainsKey("csLogo") ? 0 : int.Parse(keyDic["csLogo"].ToString().Trim());
                    // 合并Logo单元格
                    if (rowspan > 0 && colspan > 0)
                        dataCells.Merge(row, col, rowspan, colspan);

                    Array Logo = Array.CreateInstance(typeof(Stream), new int[1] { 1 }, new int[1] { 1 });
                    string logoPath = string.Format(@"Images\Logo\Logo.png");
                    logoPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, logoPath);
                    Logo.SetValue(Util.Common.FileHelper.FileToStream(logoPath), 1);
                    if (rowspan > 1)
                        dataSheet.Pictures.Add(row, col, (Stream)Logo.GetValue(1), 80, 70);
                    else
                        dataSheet.Pictures.Add(row, col, (Stream)Logo.GetValue(1), 52, 13);

                    for (int rh = row; rh <= row + rowspan - 1; rh++)
                    {
                        for (int ch = col; ch <= col + colspan - 1; ch++)
                        {
                            dataCells[rh, ch].SetStyle(ReportHeadStyle);
                        }
                    }
                }

                int rw = 0;
                foreach (var item in keyDic)
                {
                    if (item.Value.ToLower() == "true")
                    {
                        string itemKey = item.Key.ToString().Trim().ToLower();
                        string itemVal = string.Empty;
                        string itemCell = keyDic.ContainsKey("p" + item.Key.ToString().Trim()) ? 
                            keyDic["p" + item.Key.ToString().Trim()].ToString().Trim() : "";
                        var itemExpression = ReportParam.Where(dr => dr.sqlName.ToLower().Replace("[", "").Replace("]", "") 
                                    == item.Key.ToString().Trim().ToLower()).FirstOrDefault();
                        string itemTitle = (itemExpression == null ? "" : string.IsNullOrWhiteSpace(itemExpression.expression1) ? "" : 
                            itemExpression.expression1.ToString().Trim() + '\n' + (string.IsNullOrWhiteSpace(itemExpression.expression2) ? "" : 
                            itemExpression.expression2.ToString().Trim()));
                        if (itemKey == "costcentercode")
                            itemVal = keyDic["costCenterCode"].ToString().Trim();
                        else if (itemKey == "suppliercode")
                            itemVal = keyDic["supplierName"].ToString().Trim();
                        else if (itemKey == "startdate")
                            itemVal = keyDic["startDate"].ToString().Trim();
                        else if (itemKey == "period")
                            itemVal = dateList == null ? "" : dateList[number].orderDate.ToString().Trim();

                        if (!string.IsNullOrWhiteSpace(itemCell))
                        {
                            int rsItemCell = !keyDic.ContainsKey(("rs" + item.Key.ToString().Trim())) ? 0 : int.Parse(keyDic[("rs" + item.Key.ToString().Trim())].ToString().Trim());
                            int csItemCell = !keyDic.ContainsKey(("cs" + item.Key.ToString().Trim())) ? 0 : int.Parse(keyDic[("cs" + item.Key.ToString().Trim())].ToString().Trim());

                            //合并标题单元格
                            if (!string.IsNullOrWhiteSpace(itemCell) && rsItemCell > 0 && csItemCell > 0)
                            {
                                dataCells.Merge(dataCells[itemCell].Row, dataCells[itemCell].Column, rsItemCell, csItemCell);
                                rw = Math.Max(dataCells[itemCell].Row + rsItemCell, rw);
                            }
                            //标题单元格赋值
                            dataCells[itemCell].Value = itemTitle.ToString().Trim() + itemVal;
                            // 标题需要跨列显示，折行
                            if (rsItemCell > 1)
                            {
                                titleStyle.IsTextWrapped = true;
                                ReportHeadStyle.IsTextWrapped = true;
                            }
                            // 设置单元格格式
                            if (itemKey == "address" || itemKey == "pocode")
                            {
                                titleStyle.Font.IsBold = false;
                                titleStyle.Font.Size = 10;
                                titleStyle.VerticalAlignment = TextAlignmentType.Top;
                            }
                            else if(itemKey == "suppliercode" || itemKey == "remark")
                            {
                                titleStyle.Font.IsBold = false;
                                titleStyle.Font.Size = 10;
                                titleStyle.VerticalAlignment = TextAlignmentType.Top;
                                titleStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
                                titleStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
                                titleStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
                                titleStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
                            }
                            else if(itemKey == "deliveryaddress"|| itemKey == "urgent")
                            {
                                titleStyle.Font.IsBold = false;
                                titleStyle.Font.Size = 10;
                                titleStyle.VerticalAlignment = TextAlignmentType.Top;
                                titleStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
                                titleStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
                                titleStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
                                titleStyle.SetBorder(BorderType.BottomBorder, CellBorderType.None, Color.White); 
                            }
                            else if (itemKey == "receivedby" || itemKey == "explanations")
                            {
                                titleStyle.Font.IsBold = false;
                                titleStyle.Font.Size = 10;
                                titleStyle.VerticalAlignment = TextAlignmentType.Top;
                                titleStyle.SetBorder(BorderType.TopBorder, CellBorderType.None, Color.White);
                                titleStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
                                titleStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
                                titleStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
                            }
                            else if (itemKey == "orderdate" || itemKey == "startdate" || itemKey == "period")
                            {
                                titleStyle.Font.IsBold = false;
                                titleStyle.Font.Size = 10;
                                titleStyle.VerticalAlignment = TextAlignmentType.Top;
                                titleStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
                                titleStyle.SetBorder(BorderType.RightBorder, CellBorderType.None, Color.White);
                                titleStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
                                titleStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
                            }

                            for (int rh = dataCells[itemCell].Row; rh <= dataCells[itemCell].Row + rsItemCell - 1; rh++)
                            {
                                for (int ch = dataCells[itemCell].Column; ch <= dataCells[itemCell].Column + csItemCell - 1; ch++)
                                {
                                    if (itemKey == "reporttitle")
                                        dataCells[rh, ch].SetStyle(ReportHeadStyle);
                                    else
                                        dataCells[rh, ch].SetStyle(titleStyle);
                                }
                            }
                        }
                        titleStyle.IsTextWrapped = false;
                        ReportHeadStyle.IsTextWrapped = false;

                    }
                }
                
                int i = rw+1;
                // 报告明细标题
                string[] arr = title.Split(';');
                for (int a = 0; a < arr.Length; a++)
                {
                    dataCells[i, a].PutValue(arr[a]);
                    dataCells[i, a].SetStyle(subTitleStyle);
                }
                decimal netSum = 0;
                decimal taxSum = 0;

                if (rawData != null)
                {
                    netSum = 0;
                    taxSum = 0;
                    for (int b = 0; b < rawData.Count; b++)
                    {
                        i++;
                        dataCells[i, 0].PutValue(rawData[b].no);
                        dataCells[i, 0].SetStyle(intStyle);
                        dataCells[i, 1].PutValue(rawData[b].itemCode);
                        dataCells[i, 1].SetStyle(dataStyle);
                        dataCells[i, 2].PutValue(rawData[b].itemName);
                        dataCells[i, 2].SetStyle(dataStyle);
                        dataCells[i, 3].PutValue(rawData[b].unit);
                        dataCells[i, 3].SetStyle(dataStyle);
                        dataCells[i, 4].PutValue(rawData[b].itemPrice);
                        dataCells[i, 4].SetStyle(dataStyle);
                        dataCells[i, 5].PutValue(rawData[b].quantity);
                        dataCells[i, 5].SetStyle(qtyStyle);
                        dataCells[i, 6].PutValue(rawData[b].itemPrice * decimal.Parse(rawData[b].quantity.ToString("#0.0")));
                        dataCells[i, 6].SetStyle(dataStyle);
                        dataCells[i, 7].PutValue(rawData[b].remark);
                        dataCells[i, 7].SetStyle(dataStyle);
                        netSum += rawData[b].itemPrice * decimal.Parse(rawData[b].quantity.ToString("#0.0"));
                        taxSum += rawData[b].itemPrice * (1 + rawData[b].tax) * decimal.Parse(rawData[b].quantity.ToString("#0.0"));
                    }
                }
                intStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
                qtyStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
                dataStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
                for (int x = 0; x <= 7; x++)
                {
                    if (x == 0)
                        dataCells[i, x].SetStyle(intStyle);
                    else if (x == 5)
                        dataCells[i, x].SetStyle(qtyStyle);
                    else
                        dataCells[i, x].SetStyle(dataStyle);
                }

                i = i + 2;
                reportFootStyle.Custom = "#,###.00";
                dataCells[i, 2].PutValue("THÀNH TIỀN/TOTAL:");
                dataCells.Merge(i, 2, 1, 2);
                dataCells[i, 4].PutValue(netSum);
                dataCells.Merge(i, 4, 1, 2);
                for (int x = 2; x <= 5; x++)
                    dataCells[i, x].SetStyle(reportFootStyle);

                i = i + 1;
                dataCells[i, 2].PutValue("THUẾ/ VAT (x..%)");
                dataCells.Merge(i, 2, 1, 2);
                dataCells[i, 4].PutValue(taxSum - netSum);
                dataCells.Merge(i, 4, 1, 2);
                for (int x = 2; x <= 5; x++)
                    dataCells[i, x].SetStyle(reportFootStyle);

                i = i + 1;
                dataCells[i, 2].PutValue("TỔNG TIỀN / GRAND TOTAL:");
                dataCells.Merge(i, 2, 1, 2);
                dataCells[i, 4].PutValue(taxSum);
                dataCells.Merge(i, 4, 1, 2);
                for (int x = 2; x <= 5; x++)
                    dataCells[i, x].SetStyle(reportFootStyle);

                i = i + 2;
                dataCells[i, 0].PutValue("Người đề nghị" + "\n" + "Requestor" + "\n\n\n" + "Ngày/Date:__________");
                dataCells.Merge(i, 0, 6, 2);

                dataCells[i, 2].PutValue("Trưởng phòng SCMD" + "\n" + "SCMD Manager" + "\n\n\n" + "Ngày/Date:__________");
                dataCells.Merge(i, 2, 6, 1);

                dataCells[i, 3].PutValue("Giám đốc điều hành" + "\n" + "Operation Director" + "\n\n\n" + "Ngày/Date:__________");
                dataCells.Merge(i, 3, 6, 2);

                dataCells[i, 5].PutValue("Giám đốc tài chính" + "\n" + "Finance Manager" + "\n\n\n" + "Ngày/Date:__________");
                dataCells.Merge(i, 5, 6, 2);

                dataCells[i, 7].PutValue("Giám đốc chi nhánh" + "\n" + "Branch Manager" + "\n\n\n" + "Ngày/Date:__________");
                dataCells.Merge(i, 7, 6, 2);


                int y = i;
                for (int x = 0; x <= 8; x++)
                {
                    for (int z = y; z <= y + 5; z++)
                    {
                        dataCells[z, x].SetStyle(reportFootStyle);
                    }
                }

                i = i + 7;
                dataCells[i, 0].PutValue("Khi lập hóa đơn , vui lòng ghi đúng thông tin như sau/ Invoice has to established it to us as belllow:\n"
                    + "CÔNG TY TNHH ADEN SERVICES VIỆT NAM   CHI NHÁNH HÀ NỘI \n "
                    + "Tầng 9 tòa nhà Coalimex, số 33 Phố Tràng Thi, P.Trần Hưng Đạo, Q.Hoàn Kiếm, TP.Hà Nội, Việt Nam\n"
                    + "MST: 0301466475 001(đối với dòng MST là dòng kẻ ngang) \n "
                    + "MST: 0301466475001(đối với dòng MST là ô trống)");
                dataCells.Merge(i, 0, 6, 9);

                int s = i;
                for (int x = 0; x <= 8; x++)
                {
                    for (int z = s; z <= s + 5; z++)
                    {
                        dataCells[z, x].SetStyle(reportFootStyle);
                    }
                }
                #endregion
                dataSheet.Name = "PO" + 
                    (dateList==null? (keyDic.ContainsKey("startDate")? keyDic["startDate"].Replace("-", "") : ""):
                    (!string.IsNullOrWhiteSpace(dateList[number].orderDate) ? dateList[number].orderDate.Replace("-", "") : "")) + 
                    keyDic["appType"];

                dataSheet.AutoFitColumns();
                dataCells.SetColumnWidth(1, 18);
                dataCells.SetColumnWidth(2, 26);
                dataCells.SetColumnWidth(4, 18);
                dataCells.SetColumnWidth(6, 15);
                dataCells.SetColumnWidth(7, 15);
                dataSheet.AutoFitRows();
                dataSheet.Protect(0, "123abc", "");
            }
            return excel.SaveToStream();
        }

        #endregion

        /// <summary>
        /// 项目点日报告 - New Excel
        /// </summary>
        /// <param name="keyDic"></param>
        /// <param name="MainData"></param>
        /// <param name="i"></param>
        /// <param name="excel"></param>
        /// <param name="dataSheet"></param>
        /// <param name="dataCells"></param>
        /// <param name="dataStyle"></param>
        /// <param name="TotalTitleStyle"></param>
        /// <param name="TotalDataStyle"></param>
        /// <returns></returns>
        public static MemoryStream GetVNMICReport(Dictionary<string, string> keyDic, List<SqlReportParam> ReportParam, List<mic> MainData, string title,
             Workbook excel, Worksheet dataSheet, int sheetNo)
        {
           
            dataSheet = ExcelTemplateStyle(excel, dataSheet, sheetNo, out Cells dataCells,
                        out Style ReportHeadStyle, out Style titleStyle, out Style subTitleStyle, out Style dataStyle,
                        out Style qtyStyle, out Style TotalTitleStyle, out Style TotalDataStyle);
            // 设置报告Logo
            if (keyDic.ContainsKey("pLogo") && !string.IsNullOrWhiteSpace(keyDic["pLogo"]))
            {

                string logo = keyDic["pLogo"].ToString().Trim();
                int col = dataCells[logo].Column;
                int row = dataCells[logo].Row;

                int rowspan = !keyDic.ContainsKey("rsLogo") ? 0 : int.Parse(keyDic["rsLogo"].ToString().Trim());
                int colspan = !keyDic.ContainsKey("csLogo") ? 0 : int.Parse(keyDic["csLogo"].ToString().Trim());
                // 合并Logo单元格
                if (rowspan > 0 && colspan > 0)
                    dataCells.Merge(row, col, rowspan, colspan);

                Array Logo = Array.CreateInstance(typeof(Stream), new int[1] { 1 }, new int[1] { 1 });
                string logoPath = string.Format(@"Images\Logo\Logo.png");
                logoPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, logoPath);
                Logo.SetValue(Util.Common.FileHelper.FileToStream(logoPath), 1);
                if (rowspan > 1)
                    dataSheet.Pictures.Add(row, col, (Stream)Logo.GetValue(1), 80, 70);
                else
                    dataSheet.Pictures.Add(row, col, (Stream)Logo.GetValue(1), 52, 13);

                for (int rh = row; rh <= row + rowspan - 1; rh++)
                {
                    for (int ch = col; ch <= col + colspan - 1; ch++)
                    {
                        dataCells[rh, ch].SetStyle(ReportHeadStyle);
                    }
                }
            }

            int rw = 0;
            var periodDate1Expression = ReportParam.Where(dr => dr.sqlName == "[periodDate1]").FirstOrDefault();
            var periodDate2Expression = ReportParam.Where(dr => dr.sqlName == "[periodDate2]").FirstOrDefault();
            foreach (var item in keyDic)
            {
                
                if (item.Value.ToLower() == "true")
                {
                    string itemKey = item.Key.ToString().Trim().ToLower();
                    string itemVal = string.Empty;
                 
                    string itemCell = keyDic.ContainsKey("p" + item.Key.ToString().Trim()) ? keyDic["p" + item.Key.ToString().Trim()].ToString().Trim() : "";
                    var itemExpression = ReportParam.Where(dr => dr.sqlName.ToLower().Replace("[", "").Replace("]", "") == item.Key.ToString().Trim().ToLower()).FirstOrDefault();
                    string itemTitle = (itemExpression == null ? "" : string.IsNullOrWhiteSpace(itemExpression.expression1) ? "" : itemExpression.expression1.ToString().Trim() + '\n' + (string.IsNullOrWhiteSpace(itemExpression.expression2) ? "" : itemExpression.expression2.ToString().Trim()));
                    if (itemKey == "costcentercode")
                        itemVal = keyDic["costCenterCode"].ToString().Trim();
                    else if (itemKey == "period") {
                        string periodDate1Title = periodDate1Expression == null ? "" : string.IsNullOrWhiteSpace(periodDate1Expression.expression1) ? "" : periodDate1Expression.expression1.ToString().Trim() + "\n" + (string.IsNullOrWhiteSpace(periodDate1Expression.expression2) ? "" : periodDate1Expression.expression2.ToString().Trim());
                        string periodDate2Title = periodDate2Expression == null ? "" : string.IsNullOrWhiteSpace(periodDate2Expression.expression1) ? "" : periodDate2Expression.expression1.ToString().Trim() + "\n" + (string.IsNullOrWhiteSpace(periodDate2Expression.expression2) ? "" : periodDate2Expression.expression2.ToString().Trim());

                        itemVal = periodDate1Title.ToString().Trim() + " " + keyDic["periodDate1"].ToString().Trim() + " "
                                + periodDate2Title.ToString().Trim() + " " + keyDic["periodDate2"].ToString().Trim();
                    }
                    if (!string.IsNullOrWhiteSpace(itemCell))
                    {
                        int rsItemCell = !keyDic.ContainsKey(("rs" + item.Key.ToString().Trim())) ? 0 : int.Parse(keyDic[("rs" + item.Key.ToString().Trim())].ToString().Trim());
                        int csItemCell = !keyDic.ContainsKey(("cs" + item.Key.ToString().Trim())) ? 0 : int.Parse(keyDic[("cs" + item.Key.ToString().Trim())].ToString().Trim());

                        //合并标题单元格
                        if (!string.IsNullOrWhiteSpace(itemCell) && rsItemCell > 0 && csItemCell > 0)
                        {
                            dataCells.Merge(dataCells[itemCell].Row, dataCells[itemCell].Column, rsItemCell, csItemCell);
                            rw = Math.Max(dataCells[itemCell].Row + rsItemCell, rw);
                        }
                        //标题单元格赋值
                        dataCells[itemCell].Value = itemTitle.ToString().Trim() + itemVal;

                        // 标题需要跨列显示，折行
                        if (rsItemCell > 1)
                        {
                            titleStyle.IsTextWrapped = true;
                            ReportHeadStyle.IsTextWrapped = true;
                        }
                        // 设置单元格格式
                        if (itemKey == "period")
                        {
                            titleStyle.Font.IsBold = false;
                            titleStyle.Font.Size = 10;
                            titleStyle.SetBorder(BorderType.RightBorder, CellBorderType.None, Color.White);
                            titleStyle.SetBorder(BorderType.LeftBorder, CellBorderType.None, Color.White);
                            titleStyle.SetBorder(BorderType.TopBorder, CellBorderType.None, Color.White);
                            titleStyle.SetBorder(BorderType.BottomBorder, CellBorderType.None, Color.White);
                        }
                        else if(itemKey == "costcentercode")
                        {
                            titleStyle.Font.IsBold = true;
                            titleStyle.Font.Size = 10;
                            titleStyle.SetBorder(BorderType.RightBorder, CellBorderType.None, Color.White);
                            titleStyle.SetBorder(BorderType.LeftBorder, CellBorderType.None, Color.White);
                            titleStyle.SetBorder(BorderType.TopBorder, CellBorderType.None, Color.White);
                            titleStyle.SetBorder(BorderType.BottomBorder, CellBorderType.None, Color.White);
                        }
                        if(itemKey == "period" || itemKey == "costcentercode")
                            dataCells[itemCell].SetStyle(titleStyle);
                        else if(itemKey == "reporttitle")
                            dataCells[itemCell].SetStyle(ReportHeadStyle);

                        for (int rh = dataCells[itemCell].Row; rh <= dataCells[itemCell].Row + rsItemCell - 1; rh++)
                        {
                            for (int ch = dataCells[itemCell].Column; ch <= dataCells[itemCell].Column + csItemCell - 1; ch++)
                            {
                                if(itemKey == "reporttitle")
                                    dataCells[rh, ch].SetStyle(ReportHeadStyle);
                                else if (itemKey == "period" || itemKey == "costcentercode")
                                    dataCells[rh, ch].SetStyle(titleStyle);
                            }
                        }
                    }
                    titleStyle.IsTextWrapped = false;
                    ReportHeadStyle.IsTextWrapped = false;
                }
            }

            int i = rw;

            decimal TRevenue = 0;
            decimal TCost = 0;

            // 报告明细标题
            string[] arr = title.Split(';');
            for (int a = 0; a < arr.Length; a++)
            {
                dataCells[i, a].PutValue(arr[a]);
                dataCells[i, a].SetStyle(subTitleStyle);
            }

            if (MainData != null)
            {
                
                for (int b = 0; b < MainData.Count; b++)
                {
                    i++;
                    decimal avgCost = MainData[b].mealQty == 0 ? 0 : MainData[b].dailyCost / MainData[b].mealQty;
                    decimal avgRev = MainData[b].mealQty == 0 ? 0 : MainData[b].dailyRevenue / MainData[b].mealQty;
                    decimal dailyRate = avgRev == 0 ? 0 : (avgCost / avgRev);

                    dataCells[i, 0].PutValue(MainData[b].orderDate);
                    dataCells[i, 1].PutValue(MainData[b].dailyRevenue);
                    dataCells[i, 1].SetStyle(dataStyle);

                    dataCells[i, 2].PutValue(MainData[b].mealQty);
                    dataCells[i, 2].SetStyle(dataStyle);

                    dataCells[i, 3].PutValue(MainData[b].dailyCost);
                    dataCells[i, 3].SetStyle(dataStyle);

                    dataCells[i, 4].PutValue(avgCost);
                    dataCells[i, 4].SetStyle(dataStyle);

                    dataCells[i, 5].PutValue(avgRev);
                    dataCells[i, 5].SetStyle(dataStyle);

                    dataCells[i, 6].PutValue(dailyRate.ToString("p"));
                    TRevenue += MainData[b].dailyRevenue;
                    TCost += MainData[b].dailyCost;
                }
            }
            i += 2;
            dataCells[i, 0].PutValue("Total:");
            dataCells[i, 0].SetStyle(TotalTitleStyle);

            dataCells[i, 1].PutValue(TRevenue);
            dataCells[i, 1].SetStyle(TotalDataStyle);

            dataCells[i, 3].PutValue(TCost);
            dataCells[i, 3].SetStyle(TotalDataStyle);

            dataCells[i, 5].PutValue("Actual:");
            dataCells[i, 5].SetStyle(TotalTitleStyle);

            dataCells[i, 6].PutValue((TRevenue == 0 ? 0 : TCost / TRevenue).ToString("p"));
            dataCells[i, 6].SetStyle(TotalDataStyle);

            dataSheet.AutoFitRows();
            dataSheet.AutoFitColumns(0,3);
            dataSheet.Protect(0, "123abc", "");
            return excel.SaveToStream();
        }
       
        /// <summary>
        /// 报告允许显示的参数列表
        /// </summary>
        /// <param name="keyDic"></param>
        /// <returns></returns>
        public static List<SqlReportParam> ParamControl(Dictionary<string, string> keyDic)
        {
            try
            {
                if (!keyDic.ContainsKey("userGuid") || !keyDic.ContainsKey("reportGuid"))
                    return null;
                //string sql = " SELECT A.SQLFIELD AS MODELNAME FROM REPORTFIELDS (NOLOCK) A, REPORTMAST (NOLOCK) B "
                //    + " WHERE A.REPORTGUID = B.REPORTGUID AND B.MENUNAME = '{0}' "
                //    + " AND ISNULL(A.PARAMSORT,'')<> '' "
                //    + " AND GETDATE() BETWEEN ISNULL(A.STARTDATE,'2000-01-01') "
                //    + " AND ISNULL(A.ENDDATE, '2099-12-01') ";
                string sql = "select a1.SqlField as ModelName,lower(a1.FieldType) as FieldType,a1.UMDType from "
                    + " reportfields a1(nolock) left join "
                    + " (tblApps a2 (nolock) join tblAppUserData a3 (nolock) on a2.guid= a3.appguid "
                    + " join tblRoleUserData a4 (nolock) on a3.RoleGuid=a4.RoleGuid "
                    + " and isnull(a3.userguid,a4.userguid)= '{0}') "
                    + " on a1.reportguid = isnull(a2.reportguid, '') and isnull(a1.umdtype,'a') = isnull(a3.type, 'b') "
                    + " where isnull(a1.ParamSort,'')<> '' and GETDATE() BETWEEN ISNULL(convert(varchar(10),A1.STARTDATE,23),'2000-01-01') "
                    + " AND ISNULL(convert(varchar(10),A1.ENDDATE,23), '2099-12-01') and a1.reportGuid = '{1}' "
                    + " order by a1.paramSort ;";
                sql = string.Format(sql, keyDic["userGuid"], keyDic["reportGuid"]);

                List<SqlReportParam> ParamList = SqlServerHelper.GetEntityList<SqlReportParam>(SqlServerHelper.salesorderConn(), sql);
                
                return ParamList;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Export Exist Report Template
        /// </summary>
        /// <param name="keyDic"></param>
        /// <returns></returns>
        public static MemoryStream GetVNPOTempt(Dictionary<string, string> keyDic)
        {
            MemoryStream ms = new MemoryStream();

            string sql = GetReportMast(!keyDic.ContainsKey("reportGuid") ? "" : keyDic["reportGuid"]);

            List<ReportMast> ReportMast = SqlServerHelper.GetEntityList<ReportMast>(SqlServerHelper.salesorderConn(), sql);

            if (ReportMast == null || ReportMast.Count == 0)
                return null;          
            if (keyDic.ContainsKey("reportGuid"))
                ms = GetVNPOTemplate(keyDic, ReportMast);
            return ms;
        }
        /// <summary>
        ///  VN Daily PO Template (New)
        /// </summary>
        /// <param name="keyDic"></param>
        /// <param name="ReportMast"></param>
        /// <returns></returns>
        public static MemoryStream GetVNPOTemplate(Dictionary<string, string> keyDic, List<ReportMast> ReportMast)
        {
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            HSSFWorkbook book;
           
            // 报告字段
            List<ReportFields> Fields = GetReportFields(ReportMast.FirstOrDefault().reportGuid)
                .Select(q =>
            {
                if (string.IsNullOrWhiteSpace(q.cellName))
                    return q;

                int[] pos = getCell(q.cellName.ToStringTrim());
                q.rowIndex = pos[0];
                q.columnIndex = pos[1];
                return q;
            }).ToList();
            // 数据明细
            string strMainData = ReportMast.FirstOrDefault().sql;
            // 参数列表
            List<ReportFields> reportParams = Fields.Where(dr => !string.IsNullOrWhiteSpace(dr.paramSort)).ToList<ReportFields>();
            // 报表字段
            var reportFields = Fields.Where(dr => !string.IsNullOrWhiteSpace(dr.cellName))
                .GroupBy(dr => dr.detailAction).Select(dr =>
                new {
                    dr.Key,
                    fields = string.Join(",", dr.Select(q => q.fieldName.Trim()).ToArray()),
                    values = dr.Select(q => q)
                }).ToList();
            var reportFields_bak = reportFields;

            string excelPath = ConfigurationManager.AppSettings["CustomizedExcel"].ToString()+ ReportMast.FirstOrDefault().reportName;
            // SQLExcel 报告无DB字段
            if (!reportFields_bak.Any())
                excelPath = ConfigurationManager.AppSettings["SQLExcel"].ToString();
            //string filePath = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, @"ToReports\Models\VN\DailyPO.xls");
            string filePath = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, excelPath);
            
            using (var readFile = File.OpenRead(filePath))
            {
                book = new HSSFWorkbook(readFile);
            }
            #region Added
            //Dictionary<string, string> dicFields = new Dictionary<string, string>();
            //foreach(var oneFields in reportFields)
            //{
            //    string strFields = "";
            //    foreach(var field in oneFields)
            //    {
            //        strFields += "," + field.fieldName;
            //    }
            //    if(!string.IsNullOrWhiteSpace(strFields))
            //        strFields = strFields.Substring(1);

            //    dicFields.Add(oneFields.Key, strFields);
            //}
            #endregion

            // 将参数传入主数据
            if (reportParams != null && reportParams.Count > 0)
            {
                string paramNames = string.Empty;
                foreach (var item in reportParams)
                {
                    string param = item.fieldName.ToString().Trim();
                   if(keyDic.ContainsKey(param))
                        paramNames += "," + keyDic[param];
                    else
                        paramNames = !keyDic.ContainsKey("modelName") ? "," : "," + keyDic["modelName"];
                }
                // 主数据传入参数
                strMainData = string.Format(strMainData, paramNames.Substring(1).Split(','));
            }
            // 主数据
            var dt = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), strMainData);
            // 主数据为空时，终止程序
            if (dt == null || !dt.AsEnumerable().Any())
                return ms;

            if (!reportFields.Any())
            {
                Fields = new List<ReportFields>();
                string cell = keyDic.ContainsKey("startAddr")? 
                    (string.IsNullOrWhiteSpace(keyDic["startAddr"])? "A1": keyDic["startAddr"]) : "A1";
                int rowIndex = getCell(cell)[0];
                int columnIndex = getCell(cell)[1];
                int firstColumnIndex = getCell(cell)[1];
                foreach (DataColumn col in dt.Columns)
                {
                    ReportFields field = new ReportFields();
                    field.cellName = string.Empty;
                    field.rowIndex = rowIndex;
                    field.columnIndex = columnIndex++;
                    field.detailAction = field.columnIndex == firstColumnIndex ? "copy" : "list";
                    field.fieldName = col.ColumnName;
                    Fields.Add(field);
                }
                // 报表字段
                reportFields = Fields.GroupBy(dr => dr.detailAction).Select(dr =>
                    new {
                        dr.Key,
                        fields = string.Join(",", dr.Select(q => q.fieldName.Trim()).ToArray()),
                        values = dr.Select(q => q)
                    }).ToList();
            }
            // 获取主表标题名列表
            List<string> columnNameList = new List<string>();
            foreach (DataColumn col in dt.Columns)
            {
                columnNameList.Add(col.ColumnName);//获取到DataColumn列对象的列名
            }
            var mainData = dt.toList(columnNameList.ToArray());

            // MainData GroupBy sheetNameGrp
            string sheetNameGrp = ReportMast.FirstOrDefault().sheetName;
            string groupby = "1";
            if (!string.IsNullOrWhiteSpace(sheetNameGrp))
                groupby = string.Format("new ({0})", sheetNameGrp);
             // mainData为DataTable
             //var sheets = mainData.AsEnumerable().AsQueryable()
             //        .GroupBy("new("+ "it[\"transDate\"] as transDate, it[\"supplierCode\"] as supplierCode, it[\"appType\"] as menuType" + ")", "it")
             //        .Select("new (it.Key as key,it as values)");

            var sheets = mainData.GroupBy(groupby, "it")
                    .Select("new (it.Key as key,it as values)");

            
            foreach (dynamic sheet in sheets)
            {
                Type type = sheet.key.GetType();
                string sheetName = string.Empty;
                string name = string.Empty;
                if (!string.IsNullOrWhiteSpace(sheetNameGrp))
                {
                    foreach (string s in sheetNameGrp.Split(','))
                    {
                        if (s == "appType" && string.IsNullOrWhiteSpace(type.GetProperty(s).GetValue(sheet.key)))
                            name = "DPO";
                        else
                            name = type.GetProperty(s).GetValue(sheet.key);
                        sheetName += "-" + name;
                    }
                    sheetName = sheetName.Replace("-", "");
                }
                if (string.IsNullOrWhiteSpace(sheetName))
                    sheetName = "Report";
                //复制sheet
                ISheet excel = CopySheet(book, 0, sheetName);

                foreach (var item in reportFields)
                {
                    string cellName = item.values.FirstOrDefault().cellName.ToString().Trim();

                    int rowNumber = item.values.FirstOrDefault().rowIndex; //getCell(cellName)[0];
                     
                    rowNumber--;
                    // 锁住第一行
                    IRow firstRow = excel.GetRow(rowNumber);
                    if (firstRow == null && !reportFields_bak.Any())
                        firstRow = excel.CreateRow(rowNumber);
                    //columnIndex = getCell(cellName)[1];

                    foreach (dynamic line in sheet.values)
                    {
                        if (!string.IsNullOrWhiteSpace(item.Key) && item.Key.ToLower() == "copy"
                            && (firstRow == null ? rowNumber : firstRow.RowNum) < rowNumber) //Recheck
                        {
                            // 复制行
                            firstRow.CopyRowTo(rowNumber);
                        }
                        IRow rw = excel.GetRow(rowNumber);
                        rowNumber++;
                        //continue;
                        // 单元格赋值
                        if (rw == null && !reportFields_bak.Any())
                            rw = excel.CreateRow(rowNumber);
                        else if(rw == null)
                            continue;
                         
                        foreach (var s in item.values)
                        {
                            cellName = s.cellName.ToString().Trim();
                            int columnIndex = s.columnIndex; //getCell(cellName)[1];
                            
                            var cellValue = line.GetType().GetProperty(s.fieldName.ToString().Trim()).GetValue(line);
                            // 单元格赋值 - 定义单元格行
                            if (string.IsNullOrWhiteSpace(item.Key))
                                rw = excel.GetRow(s.rowIndex - 1);
                                //rw = excel.GetRow(getCell(cellName)[0]-1);
                            try
                            {
                               double.TryParse(cellValue.ToString().Trim(), out double result);
                                if (result == 0)
                                {
                                    cellValue = cellValue.ToString().Trim();
                                }
                                else
                                {
                                    cellValue = result;
                                }
                            }
                            catch
                            {
                               cellValue = Aden.Util.Common.BasicConverter.ToStringTrim(cellValue);
                            }
                            try
                            {
                               rw.GetCell(columnIndex).SetCellValue(cellValue);
                            }
                            catch
                            {
                               rw.CreateCell(columnIndex).SetCellValue(cellValue);
                            }
                        }
                    }
                    //列表尾插入一行
                    if(!string.IsNullOrWhiteSpace(item.Key) && item.Key.ToLower() == "list")
                    {
                        int lastRowNumber = excel.LastRowNum;
                        if (lastRowNumber < rowNumber)
                            lastRowNumber = rowNumber + 1;
                        excel.ShiftRows(rowNumber, lastRowNumber, 1, true, false);
                    }
                }
                // 自动更新Excel公式
                excel.ForceFormulaRecalculation = true;
                if(!string.IsNullOrWhiteSpace(ReportMast.FirstOrDefault().password))
                    excel.ProtectSheet(ReportMast.FirstOrDefault().password);
            }
            book.SetSheetHidden(0, true);
           
            book.Write(ms);
            return ms;
        }
        /// <summary>
        /// 返回字段X，Y轴值
        /// </summary>
        /// <returns></returns>
        public static int[] getCell(string cellName)
        {
            int rowIndex =  int.Parse(Regex.Replace(cellName, "[a-z]", "", RegexOptions.IgnoreCase));
            int columnIndex = ColumnToIndex(Regex.Replace(cellName, @"\d", ""));

            int[] posArr = new int[] { rowIndex, columnIndex};
            return posArr;
        }
        /// <summary>
        /// 用于excel表格中列号字母转成列索引，从1对应A开始
        /// </summary>
        /// <param name="column">列号</param>
        /// <returns>列索引</returns>
        private static int ColumnToIndex(string column)
        {
            if (!Regex.IsMatch(column.ToUpper(), @"[A-Z]+"))
            {
                throw new Exception("Invalid parameter");
            }
            int index = 0;
            char[] chars = column.ToUpper().ToCharArray();
            for (int i = 0; i < chars.Length; i++)
            {
                index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
            }
            return index -1;
        }

        /// <summary>
        /// 主表数据
        /// </summary>
        /// <param name="reportGuid"></param>
        /// <returns></returns>
        public static string GetReportMast(string reportGuid)
        {
            string sql = "SELECT A.reportGuid, A.sqlGuid, A.sheetName, B.sql, A.password, A.reportName from ReportMast (nolock) A, ReportSQL (nolock) B "
                + " where A.reportGuid = '{0}' and A.SQLGuid=B.Guid ";
            sql = string.Format(sql, reportGuid);
            return sql;
        }
        /// <summary>
        /// 主表参数
        /// </summary>
        /// <param name="sqlGuid"></param>
        /// <returns></returns>
        public static List<ReportFields> GetReportFields(string sqlGuid)
        {
            string sql = "SELECT sqlField as fieldName, paramSort, cellName,isnull(detailAction, '') as detailAction "
                +" from ReportFields (nolock)  "
                +" where ReportGuid = '{0}' and  getdate() between isnull(convert(datetime,startdate),'1900-01-01') " +
                " and isnull(convert(datetime,enddate),'2099-12-31')" 
                +" order by paramSort ";
            sql = string.Format(sql, sqlGuid);
            List<ReportFields> ReportFields = SqlServerHelper.GetEntityList<ReportFields>(SqlServerHelper.salesorderConn(), sql);
            return ReportFields;
        }   
        public static ISheet CopySheet(HSSFWorkbook book, int tempSheetNo, string sheetName)
        {
            ISheet sheet = book.CloneSheet(tempSheetNo);
            book.SetSheetName(book.NumberOfSheets - 1, sheetName);
            return sheet;
        }
        public static IRow NewRow(ISheet sheet, int firstRowNo, int firstRowIndex, int rowCount)
        {
            
            sheet.ShiftRows(firstRowNo, sheet.LastRowNum, 1);

            for (int j = 0; j < rowCount; j++)
            {
                IRow copyRow = CopyRow(sheet, firstRowIndex, firstRowNo++);
            }
            return null;
        }

        public static IRow CopyRow(ISheet sheet, int firstRow, int targetRow)
        {
            return sheet.CopyRow(firstRow, targetRow);
        }
    }
}
